MySQL group and merge JSON values

Question

I am using some native JSON fields to store information about some application entities in a MySQL 5.7.10 database. I can have 'N' rows per "entity" and need to roll-up and merge the JSON objects together, and any conflicting keys should replace instead of merge. I can do this through code, but if I can do it natively and efficiently in MySQL even better.

I have attempted this using a combination of GROUP_CONCAT and JSON_MERGE, but I've run into two issues:

1. JSON_MERGE won't take the results of GROUP_CONCAT as a valid argument
2. JSON_MERGE combines conflicting keys instead of replacing them. What I really need is more of a JSON_SET but with 'N' number of JSON docs instead of "key, value" notation.

Is this possible with the current MySQL JSON implementation?

Answer 1

You could do something like the following:

SELECT
    CAST(CONCAT(
        '[',
        GROUP_CONCAT(
            DISTINCT JSON_OBJECT(
                'foo', mytable.foo,
                'bar', mytable.bar
            )
        ),
        ']'
    ) AS JSON) AS myJsonArr
FROM mytable
GROUP BY mytable.someGroup;

Answer 2

1. JSON_MERGE won't take the results of GROUP_CONCAT as a valid argument

GROUP_CONCAT gives a,b,c,d, not a JSON array. Use JSON_ARRAYAGG (introduced in MySQL 5.7.22), which works just like group_concat, but gives a correct array ["a", "b", "c", "d"], that should be accepted with JSON functions.

Prior to 5.7.22, you need to use a workaround:

cast(
  concat('["',                                    // begin bracket and quote
         group_concat(`field` separator '", "'),  // separator comma and quotes
         '"]'                                     // end quote and bracket
 ) as json
)

2. JSON_MERGE combines conflicting keys instead of replacing them. What I really need is more of a JSON_SET but with 'N' number of JSON docs instead of "key, value" notation.

Use JSON_MERGE_PATCH instead, as introduced in MySQL 5.7.22. JSON_MERGE is a synonym for JSON_MERGE_PRESERVE.

See https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html.

---

Read my Best Practices for using MySQL as JSON storage.