Sign inTry Free

JSON_MERGE_PATCH

On this page

A built-in function that takes two JSON objects and merges them into a single JSON object.

Syntax

JSON_MERGE_PATCH(<json_1>, <json_2>)

Arguments

  • <json_1, <json_2: Valid JSON objects or arrays, or expressions that evaluate to valid JSON objects or arrays.

Remarks

  • If either of the two arguments is not a JSON object, the result of the merge is the second argument.

  • If both arguments are JSON objects, then:

    • If both arguments contain the same key and the value of this key in the second argument is not NULL, the result includes the value from the second argument. Otherwise, the key is excluded from the result.

    • If a key is present in only one argument, its key-value pair is included in the result.

  • If both arguments are not JSON objects, the result is NULL.

  • The JSON_MERGE_PATCH function takes two arguments at a time and can be embedded to work iteratively on multiple arguments. It merges the arguments, starting from the innermost function call until all the json arguments are merged.

  • The JSON_MERGE_PATCH function works recursively. When both arguments contain pairs with the same keys that are nested JSON objects, the function merges these nested JSON objects for that key.

Examples

The following table provides several examples of JSON_MERGE_PATCH. Select examples are provided in query format in the following sections.

Query

Result

Comments

 
SELECT JSON_MERGE_PATCH(
    '{"a": 1}', 
    '{"b": 5}');

{"a": 1, "b": 5}

Both arguments are JSON objects with unique keys, therefore both are included in the result.

 
SELECT JSON_MERGE_PATCH(
    '{"a": "Hello"}', 
    '{"a": "World"}');

{"a": "World"}

The key “a” is present in both JSON objects, therefore the value from the second argument, "World" is used.

 
SELECT JSON_MERGE_PATCH(
    '[1, 2, 3]', 
    '{"a": 5}');

{"a": 5}

The first argument is not a JSON object, therefore the result is the second argument.

 
SELECT JSON_MERGE_PATCH(
    '{"a": 10}', 
    'true');

true

The second argument is not a JSON object, therefore the result is the second argument.

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

{"a": 1}

The value of the second argument is NULL, therefore the key is not added to the result.

 
SELECT JSON_MERGE_PATCH(
    '{"a": [1, 2, 3]}', 
    '{"a": [4, 5, 6]}');

{"a": [4, 5, 6]}

Arrays are not merged, therefore the result is the second argument.

 
SELECT JSON_MERGE_PATCH(
    '{"a": 5}', 
    '{}');

{"a": 5}

The second argument is an empty object and the first argument is a JSON object, therefore the result of the merge is the first argument.

 
SELECT JSON_MERGE_PATCH(
    '{"a":{"x":1}}', 
    '{"a":{"y":2}}');

{"a": {"x": 1, "y": 2}}

The function works recursively. The values of the key "a" are both JSON objects, therefore the objects {"x":1} and {"y":2} are merged to create the result value.

 
SELECT JSON_MERGE_PATCH(
    'invalid_json', 
    '{"a": 5}');

NULL

The first argument is not a valid JSON object or array, therefore the result is NULL.

 
SELECT JSON_MERGE_PATCH(
    '{"a":{"y":1}}', 
    '{"a":{"y":2}}');

{"a":{"y":2}}

The key “a” is present in both the JSON objects and merging of the JSON objects {"y":1} and {"y":2} results in the second argument.

 
SELECT JSON_MERGE_PATCH(
    '{"a": 1}', 
    '{"b": 5}', 
    '{"c":6}’);

Error

The function takes only two arguments, therefore passing the third argument gives an Error message.

 
SELECT JSON_MERGE_PATCH(
    JSON_MERGE_PATCH(
    '{"a": 1}', 
    '{"b": 5}'), 
    '{"c":6}');

{"a":1,"b":5,"c":6}

The function works iteratively with three arguments. It merges the first two arguments and then merges the result with the third argument.

Example Data

The examples in this section use the following table and values:

CREATE TABLE json_docs (id INT, json_data JSON);


INSERT INTO json_docs VALUES
(1, '{"firstname":"Andrew"}'),
(2, '{"firstname":"Julius","lastname":"Caesar"}'),
(3, '{"firstname":"Angus","lastname":"Young"}'),
(4, '{"firstname":"Randall","lastname":"McMurphy"}'),
(5, '["Mango","Apple","Orange"]'),
(6, '{"Fruits":["Mango","Apple","Orange"]}'),
(7, '{"firstname":"Edna","lastname":"Mode"}'),
(8, '{"name":{"firstname":"Norman"}}'),
(9, '{"name":{"firstname":"John"}}'),
(10, '{"firstname": "Simon"}');
SELECT * FROM json_docs
ORDER BY id;
+------+-----------------------------------------------+
| id   | json_data                                     |
+------+-----------------------------------------------+
|    1 | {"firstname":"Andrew"}                        |
|    2 | {"firstname":"Julius","lastname":"Caesar"}    |
|    3 | {"firstname":"Angus","lastname":"Young"}      |
|    4 | {"firstname":"Randall","lastname":"McMurphy"} |
|    5 | ["Mango","Apple","Orange"]                    |
|    6 | {"Fruits":["Mango","Apple","Orange"]}         |
|    7 | {"firstname":"Edna","lastname":"Mode"}        |
|    8 | {"name":{"firstname":"Norman"}}               |
|    9 | {"name":{"firstname":"John"}}                 |
|   10 | {"firstname":"Simon"}                         |
+------+-----------------------------------------------+

Example 1

Returns the merged result, since both arguments are JSON objects with unique keys.

SELECT JSON_MERGE_PATCH(
    (SELECT json_data FROM json_docs WHERE id = 1),
    '{"lastname":"Cloud"}')
    AS Result;
+-------------------------------------------+
| Result                                    |
+-------------------------------------------+
| {"firstname":"Andrew","lastname":"Cloud"} |
+-------------------------------------------+

Example 2

Returns the value from the second argument, "Augustus”, since the key “firstname” is present in both JSON objects.

SELECT JSON_MERGE_PATCH(
    (SELECT json_data FROM json_docs WHERE id = 2), 
    '{"firstname":"Augustus"}')
    AS Result;
+----------------------------------------------+
| Result                                       |
+----------------------------------------------+
| {"firstname":"Augustus","lastname":"Caesar"} |
+----------------------------------------------+

Example 3

Returns the second argument, since the first argument is not a JSON object.

SELECT JSON_MERGE_PATCH(
    (SELECT json_data FROM json_docs WHERE id = 5), 
    '{"Fruits":"Banana"}')
    AS Result;
+---------------------+
| Result              |
+---------------------+
| {"Fruits":"Banana"} |
+---------------------+

Example 4

Returns the second argument, since arrays are not merged.

SELECT JSON_MERGE_PATCH(
    (SELECT json_data FROM json_docs WHERE id = 6), 
    '{"Fruits":["Banana","Pears","Grapes"]}')
    AS Result;
+----------------------------------------+
| Result                                 |
+----------------------------------------+
| {"Fruits":["Banana","Pears","Grapes"]} |
+----------------------------------------+

Example 5

The function works recursively. The values of the key "name" are both JSON objects, therefore the objects {"firstname":"Norman"} and {"lastname":"Lewis"} are merged to create the result value.

SELECT JSON_MERGE_PATCH(
    (SELECT json_data FROM json_docs WHERE id = 8), 
    '{"name":{"lastname":"Lewis"}}')
    AS Result;
+----------------------------------------------------+
| Result                                             |
+----------------------------------------------------+
| {"name":{"firstname":"Norman","lastname":"Lewis"}} |
+----------------------------------------------------+

Example 6

Returns the second argument, since the key “name” is present in both the JSON objects and merging of the JSON objects {"firstname":"John"} and {"firstname":"Tom"} results in the second argument.

SELECT JSON_MERGE_PATCH(
    (SELECT json_data FROM json_docs WHERE id = 9), 
    '{"name":{"firstname":"Tom"}}')
    AS Result;
+------------------------------+
| Result                       |
+------------------------------+
| {"name":{"firstname":"Tom"}} |
+------------------------------+

Example 7

The function works iteratively with three arguments. It merges the first two arguments and then merges the result with the third argument.

SELECT JSON_MERGE_PATCH(
    JSON_MERGE_PATCH(
    (SELECT json_data FROM json_docs WHERE id = 10),
    '{"lastname":"Grover"}'), 
    '{"profession":"Actor"}')
    AS Result;
+----------------------------------------------------------------+
| Result                                                         |
+----------------------------------------------------------------+
| {"firstname":"Simon","lastname":"Grover","profession":"Actor"} |
+----------------------------------------------------------------+

Example 8

Returns the merged result for all the selected columns.

SELECT JSON_MERGE_PATCH(
    json_data,'{"profession":"author"}') AS Result
FROM json_docs ORDER BY id LIMIT 4;
+---------------------------------------------------------------------+
| Result                                                              |
+---------------------------------------------------------------------+
| {"firstname":"Andrew","profession":"author"}                        |
| {"firstname":"Julius","lastname":"Caesar","profession":"author"}    |
| {"firstname":"Angus","lastname":"Young","profession":"author"}      |
| {"firstname":"Randall","lastname":"McMurphy","profession":"author"} |
+---------------------------------------------------------------------+

Example 9

The function can take two variables once they are set.

SET @x = '{"firstname":"Ben","lastname":"Parker"}',
   @y = '{"firstname":"Peter","profession":"Spiderman"}';


SELECT JSON_MERGE_PATCH(@x,@y) AS MarvelCharacter;
+--------------------------------------------------------------------+
| MarvelCharacter                                                    |
+--------------------------------------------------------------------+
| {"firstname":"Peter","lastname":"Parker","profession":"Spiderman"} |
+--------------------------------------------------------------------+

Last modified: November 18, 2024

Was this article helpful?

On this page

Was this article helpful?