💫🪗 JSON Trebuchet

Community Resources
21

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"
      }
    }
  ]
}
3 Likes
22

Thanks!

2 Likes
23

[
{
“distributorID”: “d00003”,
“distributorEmail”: “d3@mail.com”,
“items”: [
{
“name”: “Product 1”,
“distributor”: “d3”,
“qty”: 4,
“price”: 899.94
},
{
“name”: “Product 2”,
“distributor”: “d3”,
“qty”: 2,
“price”: 94.99
},
{
“name”: “Product 3”,
“distributor”: “d3”,
“qty”: 1,
“price”: 899.94
}
]
},
{
“distributorID”: “d00005”,
“distributorEmail”: “d5@mail.com”,
“items”: [
{
“name”: “Product 4”,
“distributor”: “d5”,
“qty”: 3,
“price”: 540
}
]
}
]

1 Like
24

There is a structural issue with your JSON, which makes it invalid - the second distributor has a nested object of items as opposed to an array.

Here is a corrected version with standard quotes instead of smart quotes (which also break it):

[
  {
    "distributorID": "d00003",
    "distributorEmail": "d3@mail.com",
    "items": [
      {
        "name": "Product 1",
        "distributor": "d3",
        "qty": 4,
        "price": 899.94
      },
      {
        "name": "Product 2",
        "distributor": "d3",
        "qty": 2,
        "price": 94.99
      },
      {
        "name": "Product 3",
        "distributor": "d3",
        "qty": 1,
        "price": 899.94
      }
    ]
  },
  {
    "distributorID": "d00005",
    "distributorEmail": "d5@mail.com",
    "items": [
      {
        "name": "Product 4",
        "distributor": "d5",
        "qty": 3,
        "price": 540
      }
    ]
  }
]
2 Likes
25

Thanks, I trimmed a longer version for the example and likely broke it.

1 Like
26

Do you need any further help here to query the JSON?

1 Like
27

I managed to figure it out using a sub-loop, thanks for the help.

2 Likes