Sum fields inside json array in mysql

Question

I have this table:

CREATE TABLE stackoverflow_question (
    id int NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    json_ob mediumtext default null,
    PRIMARY KEY (id)
);

I do some inserts:

insert into stackoverflow_question values(null, 'albert', '[{name: "albert1", qt: 2},{name: "albert2", qt: 2}]');
insert into stackoverflow_question values(null, 'barbara', '[{name: "barbara1", qt: 4},{name: "barbara2", qt: 7}]');
insert into stackoverflow_question values(null, 'paul', '[{name: "paul1", qt: 9},{name: "paul2", qt: 11}]');

Eventually, I will need to sort this table by total quantity. in the examples above, "paul" has quantity = 20, while "barbara" has quantity = 11. And "albert" has quantity = 4.

Is it possible to create a select statement where a new field is created on the fly? Something like this:

SELECT
SUM (loop json_ob and sum all the quantity fields) AS total_quantity,
id,
name
FROM
stackoverflow_question
ORDER BY total_quantity

forpas · Accepted Answer · 2022-03-05T13:52:14.437

2

If json_ob is actually a valid json object then you can use JSON_TABLE() to extract the quantities and aggregate:

SELECT s.*, SUM(t.qt) total_quantity 
FROM stackoverflow_question s, 
     JSON_TABLE(json_ob, '$[*]' COLUMNS (qt INTEGER PATH '$.qt')) t
GROUP BY s.id
ORDER BY total_quantity DESC;

See the demo.

edited Mar 05 '22 at 13:52

answered Mar 05 '22 at 13:39

forpas

160,666
10
38
76

This works. However, I realized that current mysql version in use is 5.*. Is there a solution for older versions? – oderfla Mar 05 '22 at 19:58
1

@oderfla no, there is no equivalent to json_table() for MySql 5.*. Maybe you can try the answers in this question: https://stackoverflow.com/questions/67737833/error-when-calling-json-table-in-mysql-5-7-compatible-amazon-aurora – forpas Mar 05 '22 at 20:11

Luuk · Answer 2 · 2022-03-05T14:22:52.390

According to jsonlint your JSON is not valid.

That's why this SQL returns an error (ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_table: "Missing a name for object member." at position 2.")

SELECT 
   j.name, j.qt
FROM JSON_TABLE('[{name: "paul1", qt: 9},{name: "paul2", qt: 11}]',
          "$[*]" COLUMNS (name varchar(20) PATH "$.name", qt int PATH "$.qt")) j ;

and this will return the values:

SELECT 
   j.name, j.qt
FROM JSON_TABLE('[{"name": "paul1", "qt": 9},{"name": "paul2", "qt": 11}]',
          "$[*]" COLUMNS (name varchar(20) PATH "$.name", qt int PATH "$.qt")) j ;

output:

name	qt
paul1	9
paul2	11

You can convert your relaxedJSON, to JSON, using tools like : www.relaxedjson.org

Sum fields inside json array in mysql

2 Answers2