CODEWITHSUNDEEP
mysqljson
ryanmovista
ryanmovista

Reputation: 219

MySQL group and merge JSON values

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?

Upvotes: 17

Views: 8406

Answers (4)

Δ O
Δ O

Reputation: 3710

  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
)
  1. 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.

Upvotes: 1

DataVader
DataVader

Reputation: 780

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;

Upvotes: 2

DataVader
DataVader

Reputation: 780

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 :(

Upvotes: 1

Manoj
Manoj

Reputation: 507

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

Upvotes: 0

Related Questions