How to merge multiple JSON (JavaScript Object Notation) values with all members preserved using the JSON_MERGE_PRESERVE() function?

JSON_MERGE_PRESERVE(json1, json2, ...) is a MySQL built-in function that merges a list of JSON values into a single JSON value with all members preserved. A JSON value can be a JSON scalar (String, Number, Boolean, or Null), a JSON array, or a JSON object.

Specified JSON value arguments are merged one at a time from left to right according to the following rules:

• If an argument is a JSON scalar, it is automatically wrapped as a JSON array.
• If a JSON array is merged with a JSON array, members of the second array are appended to the end of the first array.
• If a JSON object is merged with a JSON object, members of the second object are merged into the first object. Members of identical keys are merged with same rules listed here.

JSON value arguments must be specified as JSON encoded strings or constructed with CAST(), JSON_MERGE_PRESERVE(), or JSON_OBJECT() functions. For example:

SELECT JSON_MERGE_PRESERVE('[1, 2]', '[true, false]');
  -- +------------------------------------------------+
  -- | JSON_MERGE_PRESERVE('[1, 2]', '[true, false]') |
  -- +------------------------------------------------+
  -- | [1, 2, true, false]                            |
  -- +------------------------------------------------+

SELECT JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}');
  -- +----------------------------------------------------+
  -- | JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}') |
  -- +----------------------------------------------------+
  -- | {"id": 47, "name": "x"}                            |
  -- +----------------------------------------------------+

SELECT JSON_MERGE_PRESERVE('1', 'true');
  -- +----------------------------------+
  -- | JSON_MERGE_PRESERVE('1', 'true') |
  -- +----------------------------------+
  -- | [1, true]                        |
  -- +----------------------------------+

SELECT JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}');
  -- +---------------------------------------------+
  -- | JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}') |
  -- +---------------------------------------------+
  -- | [1, 2, {"id": 47}]                          |
  -- +---------------------------------------------+

SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }', '{ "a": 3, "c": 4 }');
  -- +-----------------------------------------------------------------+
  -- | JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }', '{ "a": 3, "c": 4 }') |
  -- +-----------------------------------------------------------------+
  -- | {"a": [1, 3], "b": 2, "c": 4}                                   |
  -- +-----------------------------------------------------------------+

Note that JSON scalar value arguments can not be specified as literals of equivalent MySQL data types. For example:

SELECT JSON_MERGE_PRESERVE('"x"', '99');
  -- +----------------------------------+
  -- | JSON_MERGE_PRESERVE('"x"', '99') |
  -- +----------------------------------+
  -- | ["x", 99]                        |
  -- +----------------------------------+

SELECT JSON_MERGE_PRESERVE('"x"', 99);
ERROR 3146 (22032): Invalid data type for JSON data in argument 2
to function json_merge_preserve; a JSON string or JSON type is required.

Reference information of the JSON_MERGE_PRESERVE() function:

JSON_MERGE_PRESERVE(json1, json2, ...): json
  Returns a JSON value by merging all JSON values given in the argument
  list with all members preserved.
  JSON scalars are wrapped as JSON arrays automatically.
  JSON objects are wrapped as JSON arrays only when merging with JSON arrays.
  JSON object members are merged if they have the same key.

Arguments, return value and availability:
  json1, json2, ...: One or more JSON values to be merged.
  json: Return value. The merged JSON value.
  Available since MySQL 5.7.

Related MySQL functions:

• JSON_MERGE_PATCH() - Merging JSON by Replacing Members
• JSON_MERGE_PRESERVE() - Merging JSON with All Members

⇒ JSON_OBJECT() - Creating JSON Object

⇐ JSON_MERGE_PATCH() - Merging JSON by Replacing Members

⇑ MySQL Functions on JSON Values

⇑⇑ MySQL Function References