Sample JSON:
{
"orderId": "ORD-12345",
"customerId": "CUST-ABC",
"timestamp": "2023-10-27T10:30:00Z",
"items": [
{
"lineItemId": "LI-001",
"productId": "PROD-A1",
"productName": "Premium Widget",
"quantity": 2,
"pricePerItem": 19.99,
"distributor": {
"id": "DIST-01",
"name": "Widgets Inc.",
"email": "orders@widgetsinc.com"
}
},
{
"lineItemId": "LI-002",
"productId": "PROD-B1",
"productName": "Standard Gadget",
"quantity": 5,
"pricePerItem": 5.50,
"distributor": {
"id": "DIST-02",
"name": "Gadget Supply Co.",
"email": "sales@gadgetsupply.co"
}
},
{
"lineItemId": "LI-003",
"productId": "PROD-A2",
"productName": "Deluxe Widget",
"quantity": 1,
"pricePerItem": 45.00,
"distributor": {
"id": "DIST-01",
"name": "Widgets Inc.",
"email": "orders@widgetsinc.com"
}
},
{
"lineItemId": "LI-004",
"productId": "PROD-C1",
"productName": "Basic Thingamajig",
"quantity": 10,
"pricePerItem": 1.25,
"distributor": {
"id": "DIST-03",
"name": "Things R Us",
"email": "orders@thingsrus.net"
}
},
{
"lineItemId": "LI-005",
"productId": "PROD-B2",
"productName": "Advanced Gadget",
"quantity": 3,
"pricePerItem": 15.00,
"distributor": {
"id": "DIST-02",
"name": "Gadget Supply Co.",
"email": "sales@gadgetsupply.co"
}
}
]
}
JSONata query you can use to group by distributor ID:
items {
distributor.id: $append($[$.distributor.id = distributor.id], $)
}
Sample result:
{
"DIST-01": [
{
"lineItemId": "LI-001",
"productId": "PROD-A1",
"productName": "Premium Widget",
"quantity": 2,
"pricePerItem": 19.99,
"distributor": {
"id": "DIST-01",
"name": "Widgets Inc.",
"email": "orders@widgetsinc.com"
}
},
{
"lineItemId": "LI-003",
"productId": "PROD-A2",
"productName": "Deluxe Widget",
"quantity": 1,
"pricePerItem": 45,
"distributor": {
"id": "DIST-01",
"name": "Widgets Inc.",
"email": "orders@widgetsinc.com"
}
},
{
"lineItemId": "LI-001",
"productId": "PROD-A1",
"productName": "Premium Widget",
"quantity": 2,
"pricePerItem": 19.99,
"distributor": {
"id": "DIST-01",
"name": "Widgets Inc.",
"email": "orders@widgetsinc.com"
}
},
{
"lineItemId": "LI-003",
"productId": "PROD-A2",
"productName": "Deluxe Widget",
"quantity": 1,
"pricePerItem": 45,
"distributor": {
"id": "DIST-01",
"name": "Widgets Inc.",
"email": "orders@widgetsinc.com"
}
}
],
"DIST-02": [
{
"lineItemId": "LI-002",
"productId": "PROD-B1",
"productName": "Standard Gadget",
"quantity": 5,
"pricePerItem": 5.5,
"distributor": {
"id": "DIST-02",
"name": "Gadget Supply Co.",
"email": "sales@gadgetsupply.co"
}
},
{
"lineItemId": "LI-005",
"productId": "PROD-B2",
"productName": "Advanced Gadget",
"quantity": 3,
"pricePerItem": 15,
"distributor": {
"id": "DIST-02",
"name": "Gadget Supply Co.",
"email": "sales@gadgetsupply.co"
}
},
{
"lineItemId": "LI-002",
"productId": "PROD-B1",
"productName": "Standard Gadget",
"quantity": 5,
"pricePerItem": 5.5,
"distributor": {
"id": "DIST-02",
"name": "Gadget Supply Co.",
"email": "sales@gadgetsupply.co"
}
},
{
"lineItemId": "LI-005",
"productId": "PROD-B2",
"productName": "Advanced Gadget",
"quantity": 3,
"pricePerItem": 15,
"distributor": {
"id": "DIST-02",
"name": "Gadget Supply Co.",
"email": "sales@gadgetsupply.co"
}
}
],
"DIST-03": [
{
"lineItemId": "LI-004",
"productId": "PROD-C1",
"productName": "Basic Thingamajig",
"quantity": 10,
"pricePerItem": 1.25,
"distributor": {
"id": "DIST-03",
"name": "Things R Us",
"email": "orders@thingsrus.net"
}
},
{
"lineItemId": "LI-004",
"productId": "PROD-C1",
"productName": "Basic Thingamajig",
"quantity": 10,
"pricePerItem": 1.25,
"distributor": {
"id": "DIST-03",
"name": "Things R Us",
"email": "orders@thingsrus.net"
}
}
]
}