MySQL 5.7 JSON_MERGE combine with GROUP_CONCAT

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?

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.

StackExchange Database Administrators Q#153000, answer score: 4