# JSON\_MERGE\_PATCH

The `JSON_MERGE_PATCH` function is a built-in function that takes two JSON objects and merges them into a single JSON object.

## Syntax

```sql
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:

```sql
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"}');
```

```sql
SELECT * FROM json_docs
ORDER BY id;

```

```output

+------+-----------------------------------------------+
| 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.

```sql
SELECT JSON_MERGE_PATCH(
    (SELECT json_data FROM json_docs WHERE id = 1),
    '{"lastname":"Cloud"}')
    AS Result;

```

```output

+-------------------------------------------+
| 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.

```sql
SELECT JSON_MERGE_PATCH(
    (SELECT json_data FROM json_docs WHERE id = 2), 
    '{"firstname":"Augustus"}')
    AS Result;

```

```output

+----------------------------------------------+
| Result                                       |
+----------------------------------------------+
| {"firstname":"Augustus","lastname":"Caesar"} |
+----------------------------------------------+

```

## Example 3

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

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

```

```output

+---------------------+
| Result              |
+---------------------+
| {"Fruits":"Banana"} |
+---------------------+

```

## Example 4

Returns the second argument, since arrays are not merged.

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

```

```output

+----------------------------------------+
| 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.

```sql
SELECT JSON_MERGE_PATCH(
    (SELECT json_data FROM json_docs WHERE id = 8), 
    '{"name":{"lastname":"Lewis"}}')
    AS Result;

```

```output

+----------------------------------------------------+
| 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.

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

```

```output

+------------------------------+
| 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.

```sql
SELECT JSON_MERGE_PATCH(
    JSON_MERGE_PATCH(
    (SELECT json_data FROM json_docs WHERE id = 10),
    '{"lastname":"Grover"}'), 
    '{"profession":"Actor"}')
    AS Result;

```

```output

+----------------------------------------------------------------+
| Result                                                         |
+----------------------------------------------------------------+
| {"firstname":"Simon","lastname":"Grover","profession":"Actor"} |
+----------------------------------------------------------------+
```

## Example 8

Returns the merged result for all the selected columns.

```sql
SELECT JSON_MERGE_PATCH(
    json_data,'{"profession":"author"}') AS Result
FROM json_docs ORDER BY id LIMIT 4;

```

```output

+---------------------------------------------------------------------+
| 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.

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

SELECT JSON_MERGE_PATCH(@x,@y) AS MarvelCharacter;

```

```output

+--------------------------------------------------------------------+
| MarvelCharacter                                                    |
+--------------------------------------------------------------------+
| {"firstname":"Peter","lastname":"Parker","profession":"Spiderman"} |
+--------------------------------------------------------------------+

```

***

Modified at: June 16, 2026

Source: [/cloud/reference/sql-reference/json-functions/json-merge-patch/](https://docs.singlestore.com/cloud/reference/sql-reference/json-functions/json-merge-patch/)

(An index of the documentation is available at /llms.txt)