MySQL 5.7 JSON_MERGE combine with GROUP_CONCAT

concatjsonmergemysql-5.7

we have table with JSON column
for example simple:

{"a": 1}
{"b": 2}

run JSON_MERGE query

select JSON_MERGE('{"a": 1}','{"b": 2}')

result correct:

{"a": 1, "b": 2}

run the GROUP_CONCAT query

select GROUP_CONCAT('\'',json_text,'\'') from t_json

result 

'{"a": 1}','{"b": 2}'

but if try to combine both of them:

SELECT JSON_MERGE((select GROUP_CONCAT('\'',json_text,'\'') from t_json))

return error:

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

any ways for merge JSON documents from group operation?

Best Answer

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

Example data 

create table item (itemName varchar(200), itemProperties json);

insert into item values
('sword', '{"damage": 20, "durability": 300}'),
('magical sword', '{"damage": 30, "magical damage": {"fire": 5},
                    "durability": 400}'),
('dummy', '{}'),
('spellbook', '{"spell": "lightning bolt", "charge": 10}');

Query to merge all itemProperties together

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

Resulting JSON

{
  "spell": "lightning bolt",
  "charge": 10,
  "damage": 20,
  "durability": 300,
  "magical damage": {
    "fire": 5
  }
}

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.
Related Question