MySQL group and merge JSON values

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;

First of all, GROUP_CONCAT only returns a string, so you have to cast it. Second of all, there is a function doing exactly what you want called JSON_MERGE_PATCH(). Try the following:

SELECT
    JSON_MERGE_PATCH(
        yourExistingJson,
        CAST(
            CONCAT(
                '[',GROUP_CONCAT(myJson),']'
            )
        AS JSON)
    ) AS myJsonArray
....

Just realized your version. You would have to upgrade to 5.7.22 or higher. Is it possible in your case? If not, there may be other ways but they wont be elegant :(

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.

Aggregation of JSON Values

For aggregation of JSON values, SQL NULL values are ignored as for other data types. Non-NULL values are converted to a numeric type and aggregated, except for MIN(), MAX(), and GROUP_CONCAT(). The conversion to number should produce a meaningful result for JSON values that are numeric scalars, although (depending on the values) truncation and loss of precision may occur. Conversion to number of other JSON values may not produce a meaningful result.

I just found this in mysql docs