Search code examples
mysqlsqlmysql-5.7metabasemysql-json

mysql 5.7.12 json extract sum

I need the sum of the "total" value. But my query always returns 0

My json field:

{'damage_budget': [{"id": 1, "name": "test-one", "value": 131, "quantity": 1}, {"id": 10, "name": "test-two", "value": 460, "quantity": 1}]}

My query:

select id, sum(column ->>'$.damage_budget[*].value') from table

Solution

  • Your extract returns a JSON array, not a scalar.

    mysql> set @j = '{"damage_budget": [{"id": 1, "name": "test-one", "value": 131, "quantity": 1}, {"id": 10, "name": "test-two", "value": 460, "quantity": 1}]}';
mysql> select json_unquote(json_extract(@j, '$.damage_budget[*].value')) as v;
+------------+
| v          |
+------------+
| [131, 460] |
+------------+

    The argument to SUM() must be a scalar.

    There's no simple way to do a SUM() against a JSON array in MySQL 5.7. Solutions exist, but they involve coding complex procedures.

    You should either upgrade to MySQL 8.0 so you can use JSON_TABLE() (for example see my answer to MySql join by json key and multiply quantity for each order item and get total price), or else store your data in normal rows and columns, not JSON.

    In fact, there's no reason to store the data you show in JSON regardless of which version of MySQL you use. You should just store it in normal rows and columns.