MySQL - Merge Multiple JSON Values to a single JSON with sums

Question

I have a table that contains a JSON column in it, say for example

CREATE TABLE test(jsonValue JSON)

And I have multiple values int it:

INSERT INTO test('{"a": 1}');
INSERT INTO test('{"a": 3}');
INSERT INTO test('{"b": 4}');
INSERT INTO test('{"b": 10}');

I would like to return a result where I merge all JSONs to a single one with the sum of the values in each JSON. So Result should be

{ 
 "a": 4,
 "b": 14
}

OR, an easier solution (using JSON_MERGE_PRESERVE)

{ 
 "a": [1, 3],
 "b": [4, 10]
}

How can I do this? I have little SQL knowledge and I can't seem to figure our how to write this query. Any help will be very appreciated, thanks!

If you have a different question related to this one, please ask it in a new question. Answering with another question will not get you new answers — Machavity
– Machavity ♦, Commented Sep 29, 2020 at 14:38

Slava · Accepted Answer · 2020-09-25 15:42:45Z

2

An easier solution (using JSON_MERGE_PRESERVE and MySQL variables):

SET @json = ( SELECT CONCAT( "'", GROUP_CONCAT( jsonValue SEPARATOR "','" ), "'" ) FROM test );
SET @q    = CONCAT( "SELECT JSON_MERGE_PRESERVE(", @json, ") AS JSON_MERGE_PRESERVE;" );
PREPARE stmt FROM @q;
EXECUTE stmt;

answered Sep 25, 2020 at 15:42

Slava

9486 silver badges9 bronze badges

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

Comments

Narek · Accepted Answer · 2020-09-25 13:53:18Z

1

Somethig like this?

SELECT JSON_OBJECTAGG(f, s)
FROM (
         SELECT JSON_EXTRACT(JSON_KEYS(jsonValue), '$[0]')                                             AS f,
                SUM(JSON_EXTRACT(jsonValue, CONCAT('$.', JSON_EXTRACT(json_keys(jsonValue), '$[0]')))) as s
         FROM test
         GROUP BY f
     ) as t

Not so nice, but works.

answered Sep 25, 2020 at 13:53

Narek

3,8235 gold badges45 silver badges59 bronze badges

4 Comments

elmekiesIsrael Over a year ago

Works good thanks! How can I alter the query so that if I have {"a": 1, "b": 2} (i.e. multiple values in each row) it will still work? Since in the query we are taking the first element when extracting. When I use wildcard insteadh of $[0] it returns a list of values ["a", "b"] and I don't understand how to continue from there. since the concat does not work. I tried lots of variations with no success (posted an additional question and for some reason my post was deleted).

Narek Over a year ago

can it be {"a": 1, "b": 2, "c": 3}?

elmekiesIsrael Over a year ago

Yes, Yes it can

Narek Over a year ago

In that case this solution will not help you. You need some kind of loop on every var in your JSON. It's little hard in mysql.

Collectives™ on Stack Overflow

MySQL - Merge Multiple JSON Values to a single JSON with sums

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related