How to merge multiple JSON (JavaScript Object Notation) values by replacing members using the JSON_MERGE_PATCH() function?

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

JSON_MERGE_PATCH() uses rules defined in RFC 7396 to process each JSON value argument from left to right:

• If the first argument is not an object, it is replaced by the second argument.
• If the second argument is not an object, it replaces the first argument.
• If both arguments are objects, members of the first argument are patched by the second argument. Members of identical keys are merged with same rules listed here, except that a JSON null value member in the second argument removes the member of the same key in the result JSON object.

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

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

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

SELECT JSON_MERGE_PATCH('1', 'true');
  -- +-------------------------------+
  -- | JSON_MERGE_PATCH('1', 'true') |
  -- +-------------------------------+
  -- | true                          |
  -- +-------------------------------+

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

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

SELECT JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}');
  -- +--------------------------------------------------+
  -- | JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}') |
  -- +--------------------------------------------------+
  -- | {"a": 1}                                         |
  -- +--------------------------------------------------+

SELECT JSON_MERGE_PATCH('{"a":1}', '{"b":null}');
  -- +-------------------------------------------+
  -- | JSON_MERGE_PATCH('{"a":1}', '{"b":null}') |
  -- +-------------------------------------------+
  -- | {"a": 1}                                  |
  -- +-------------------------------------------+

SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }',
  '{ "a": 5, "d":6 }');
  -- +-------------------------------------------------------------------------------+
  -- | JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }','{ "a": 5, "d":6 }') |
  -- +-------------------------------------------------------------------------------+
  -- | {"a": 5, "b": 2, "c": 4, "d": 6}                                              |
  -- +-------------------------------------------------------------------------------+

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

SELECT JSON_MERGE_PATCH('"x"', '99');
  -- +-------------------------------+
  -- | JSON_MERGE_PATCH('"x"', '99') |
  -- +-------------------------------+
  -- | 99                            |
  -- +-------------------------------+

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

Reference information of the JSON_MERGE_PATCH() function:

JSON_MERGE_PATCH(json1, json2, ...): json
  Returns a JSON value by merging all JSON value arguments given in the
  argument list. replacing members. The first argument is replaced by
  the second argument, if one of them is not a JSON object. Members of
  the second argument are merged into the first argument if they are
  both JSON objects, except that a JSON null value member in the second
  argument removes the member of the same key in the result JSON object.

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_MERGE_PRESERVE() - Merging JSON with All Members

⇐ JSON_MERGE() - Synonym for JSON_MERGE_PRESERVE()

⇑ MySQL Functions on JSON Values

⇑⇑ MySQL Function References