CODEWITHSUNDEEP
mysqlsqljson
elmekiesIsrael
elmekiesIsrael

Reputation: 133

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

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!

Upvotes: 0

Views: 1007

Answers (2)

Slava
Slava

Reputation: 938

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;

Upvotes: 2

Narek
Narek

Reputation: 3823

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.

Upvotes: 1

Related Questions