MYSQL JSON_MERGE and Group By -

i have following table of data

what trying group by timestamp , json_merge objects. expect result following.

i expected following query work error

select timestamp, json_merge(json) data group timestamp, json_merge(json) 

the error getting

incorrect parameter count in call native function 'json_merge'

assemble desired json using string functions , cast json.

example data

create table data (json json, timestamp datetime);  insert 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 merge json values grouped timestamp

select cast(   concat('{',  -- wrap in root object '{ ... }'     group_concat(       -- strip parenthesis individual item representation       -- '{"foo": 1}' -> '"foo": 1'       substring(json, 2, length(json) - 2)),   '}') json) json, timestamp data -- skip empty json values avoid getting comma during  -- group_concat json != json_object()   group 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 snippet different json_merge(), example:
  • when 2 or more properties have same name values overwritten instead of being merged array of values
  • it can't merge objects arrays
• solution presented works objects top level entity , not arrays. can modified work arrays.
• if relies on string representation of json objects beginning , ending curly brackets {}. might change in future versions of server.