How to get the sum of the cost of goods from the json array?

Question

MySQL

The database contains the following data:

id | data
1  |  products": [{"price": 1000}, {"price": 2000}]

I need to get the amount of price (3000), I do this:

AnyModel::select(DB::raw('SUM(data->"$.products[*].price") as totalsales'));

The result is 0
How do I get the sum of all prices from the json array?

Answer 1

SELECT test.id, SUM(jsontable.price) total_price
FROM test
CROSS JOIN JSON_TABLE(test.data,
                      '$.products[*]' COLUMNS (price INT PATH '$.price')) jsontable
GROUP BY test.id

fiddle