CODEWITHSUNDEEP
mysqlmysql-json
Cathal Coffey
Cathal Coffey

Reputation: 1117

MYSQL JSON_MERGE and Group By

I have the following table of data

enter image description here

What I am trying to do is GROUP BY timestamp and JSON_MERGE the objects. What I expect as a result is the following.

enter image description here

I expected the following query to work but I just get an error

SELECT timestamp, JSON_MERGE(json)
FROM data
GROUP BY timestamp, JSON_MERGE(json)

The error I am getting is

Incorrect parameter count in the call to native function 'JSON_MERGE'

Upvotes: 0

Views: 4150

Answers (1)

Mr. Deathless
Mr. Deathless

Reputation: 1391

Assemble desired JSON using string functions and then cast it into JSON.

Example data 

create table data (json json, timestamp datetime);

insert into data values
('{"a": 1}', '2016-10-01 00:00:00'),
('{"b": 2}', '2016-10-01 00:00:00'),
('{"c": 3}', '2016-10-01 11:11:11'),
('{}', '2016-10-01 11:11:11'),
('{"c": 33}', '2016-10-01 11:11:11');

Query to merge all json values grouped by timestamp

select cast(
  concat('{',  -- wrap everything in root object '{ ... }'
    group_concat(
      -- strip parenthesis from individual item representation
      -- '{"foo": 1}' -> '"foo": 1'
      substring(json, 2, length(json) - 2)),
  '}') as json) json,
timestamp
from data
-- skip empty JSON values to avoid getting extra comma during 
-- group_concat
where json != JSON_OBJECT()  
group by timestamp;

Query result 

+------------------+---------------------+
| json             | timestamp           |
|------------------+---------------------|
| {"a": 1, "b": 2} | 2016-10-01 00:00:00 |
| {"c": 3}         | 2016-10-01 11:11:11 |
+------------------+---------------------+

Several caveats:

  • Behaviour of this snippet is different from JSON_MERGE(), for example:
    • When two or more properties have the same name their values are overwritten instead of being merged into array of values
    • It can't merge objects with arrays
  • Solution as presented only works with objects as a top level entity and not with arrays. It can be modified to work with arrays.
  • If relies on string representation of JSON objects beginning and ending with curly brackets {}. This might change in future versions of the server.

Upvotes: 2

Related Questions