MariaDB JSON_MERGE_PATCH() Function

In MariaDB, JSON_MERGE_PATCH() is a built-in function that merges two or more JSON documents and returns the merged result.

JSON_MERGE_PATCH() is compatible with RFC 7396 and used to replace the deprecated JSON_MERGE().

MariaDB JSON_MERGE_PATCH() Syntax

Here is the syntax for the MariaDB JSON_MERGE_PATCH() function:

JSON_MERGE_PATCH(json1, json2, ...)

Parameters

json1

Required. A JSON object document.

json2

Required. A JSON object document.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_MERGE_PATCH'.

Return value

The MariaDB JSON_MERGE_PATCH() function returns a JSON document that combines multiple JSON documents specified by parameters. JSON_MERGE_PATCH() performs a replacement merge, that is, when the key value is the same, only the subsequent value is retained. The rules for merging are as follows:

  1. If the first argument is not an object, the result of merging is the same as merging an empty object with the second argument.

  2. If the second argument is not an object, the result of the merge is the second argument.

  3. If both arguments are objects, the merged object has the following members:

    • All members that only exist in the first object
    • All members that only exist in the second object and whose value is not null
    • All members existing in the second object and whose value is not null, and has a corresponding member of the same key in the first object

That is, the result of merging two objects is an object. If the two parameters are of different JSON types or neither is a JSON object, the merged result is the second parameter.

If either argument is NULL, the JSON_MERGE_PATCH() function will return NULL.

MariaDB JSON_MERGE_PATCH() Examples

Here are some common examples to show the usages of the Mariadb JSON_MERGE_PATCH() function.

Non-JSON object type merging

The following demonstrates how to use the JSON_MERGE_PATCH() function to merge two JSON documents that are not JSON object types.

SELECT
    JSON_MERGE_PATCH('2', 'true') AS `2 + true`,
    JSON_MERGE_PATCH('true', '2') AS `true + 2`,
    JSON_MERGE_PATCH('[1, 2]', '2') AS `[1, 2] + 2`,
    JSON_MERGE_PATCH('2', '[1, 2]') AS `2 + [1, 2]`,
    JSON_MERGE_PATCH('[1, 2]', '[2, 3]') AS `[1, 2] + [2, 3]`;

Output:

+----------+----------+------------+------------+-----------------+
| 2 + true | true + 2 | [1, 2] + 2 | 2 + [1, 2] | [1, 2] + [2, 3] |
+----------+----------+------------+------------+-----------------+
| true     | 2        | 2          | [1, 2]     | [2, 3]          |
+----------+----------+------------+------------+-----------------+

Here we see that neither parameter is a JSON object and the JSON_MERGE_PATCH() function returns the second parameter.

Merge JSON objects

The following demonstrates how to use the JSON_MERGE_PATCH() function to merge two or more JSON objects.

SELECT JSON_MERGE_PATCH('{"x": 1, "z": 7}', '{"x": 2, "y": 3}');

Output:

+----------------------------------------------------------+
| JSON_MERGE_PATCH('{"x": 1, "z": 7}', '{"x": 2, "y": 3}') |
+----------------------------------------------------------+
| {"x": 2, "z": 7, "y": 3}                                 |
+----------------------------------------------------------+

If there is a key having a null value in the second parameter, the key will not appear in the result object.

SELECT JSON_MERGE_PATCH('{"x": 1, "z": 7}', '{"x": 2, "z": null}');

Output:

+-------------------------------------------------------------+
| JSON_MERGE_PATCH('{"x": 1, "z": 7}', '{"x": 2, "z": null}') |
+-------------------------------------------------------------+
| {"x": 2}                                                    |
+-------------------------------------------------------------+

Conclusion

In MariaDB, JSON_MERGE_PATCH() is a built-in function that merges two or more JSON documents and returns the merged result.