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

CodePudding user response：

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.

CodePudding user response：

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:

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