JSON EXTRACT SUM returning 0 instead of correct value-CodePudding

I'm trying to sum the contents of a json array in a mysql database, below is the JSON format and the query I'm running. Is there something wrong with it?

// Options JSON Format:

[
    {
        "optionId": 1,
        "optionName": "With Meat",
        "optionPrice": 2000
    },
    {
        "optionId": 2,
        "optionName": "With Veggies",
        "optionPrice": 0
    }
]

// Query:

SELECT id, SUM(options->'$[*].optionPrice') FROM table_order_items GROUP BY id;

The result is 0, when it should be 2000

While this query:

SELECT id, options->'$[*].optionPrice' FROM table_order_items;

correctly returns [2000,0]

CodePudding user response：

You need the function JSON_TABLE() to extract the prices:

SELECT t.id, 
       SUM(j.price) AS total 
FROM table_order_items t 
JOIN JSON_TABLE(
       t.options,
       '$[*]' COLUMNS(price INT PATH '$.optionPrice')
     ) j
GROUP BY t.id;

See the demo.