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

Answer 1

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.