0

I have an order_summary JSON column in my orders table with the following structure

{
  "total": 16.895,
  "products": { // products is an object of objects instead of array of objects
    "98": {
      "price": "2.400",
      "quantity": 2,
      "sub_total": 4.8,
      "product_id": 100,
      "variant_id": 98
    },
    "395": {
      "price": "3.900",
      "quantity": 1,
      "sub_total": 3.9,
      "product_id": 401,
      "variant_id": 395
    },
    "732": {
      "price": "7.695",
      "quantity": 1,
      "sub_total": 7.695,
      "product_id": 754,
      "variant_id": 732
    }
  }
}

What I am trying to do,

I have to calculate the total number of items sold for a given variant_id, So I want to fetch the sum the quantity field inside the products object.

The problem

Sadly the products field is not an array of objects instead its an object of objects. With the keys being variant_id itself. So my following query is not working

SELECT sum(order_summary->"$.products.quantity") FROM orders where order_summary->"$.products.variant_id" = 98 (which returns an empty result set)

I think this is because of the presence of the keys in the products array. So how can I tackle this?

I am using Mysql 8, kindly let me know if any more information is needed.

Shobi
  • 10,374
  • 6
  • 46
  • 82

1 Answers1

0

After some trial and error and with the help of a friend, I found the following solution,

SELECT SUM(order_summary -> '$.products."98".quantity') as sum
FROM orders
WHERE JSON_CONTAINS_PATH(order_summary, 'all', '$.products."98"')
Shobi
  • 10,374
  • 6
  • 46
  • 82