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-05 13:52:14Z

3

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 5, 2022 at 13:52

answered Mar 5, 2022 at 13:39

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

oderfla Over a year ago

This works. However, I realized that current mysql version in use is 5.*. Is there a solution for older versions?

forpas Over a year ago

@oderfla no, there is no equivalent to json_table() for MySql 5.*. Maybe you can try the answers in this question: stackoverflow.com/questions/67737833/…

Luuk · Accepted Answer · 2022-03-05 14:22:52Z

2

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

edited Mar 5, 2022 at 14:22

answered Mar 5, 2022 at 13:39

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

Collectives™ on Stack Overflow

Sum fields inside json array in mysql

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related