# JSON\_SET\_\<type>

There is a group of three JSON functions, which sets the value of a given key in a JSON map or array.

It requires the keypath to exist and will only create the last key and value if it does not exist. It will not create the entire keypath.

Maps are in the form:

```
{"item1":1, "item2":2, "item3": [3,4,5]}

```

And arrays are in the form:

```
[1, 2, 3]

```

* `JSON_SET_DOUBLE` is for all numeric data. JavaScript has only one numeric type.
* `JSON_SET_STRING` is for all text and binary types.
* `JSON_SET_JSON` is for all valid JavaScript types, including `true`, `false`, JSON maps, and lists.

```sql
JSON_SET_<type>(json, keypath, value)

```

## Arguments

* json: A valid JSON map or array, or the name of a JSON column.
* keypath: The path to the key to set the value to (comma-separated list of dictionary keys or zero-indexed array positions).
* value: the value to set.

If the keyname does not exist in a map, it will be added. If it does exist, the value will be overwritten. If an array index is past the end of the array, intervening values will be set to NULL.

## Return Value

* The original JSON document, modified to set the specified key path to the specified value.
* SQL NULL if JSON is not a valid JSON map or array.

## json\_compatibility\_level

This function allow you to set values within a JSON document which return a new, logically modified document, while leaving the original document unchanged. The `json_compatibility_level` engine variable controls the behavior of the  `json_set_<type>` functions and specifies whether the function will create missing keys if they does not exist. The default value for `json_compatibility_level` is `7.8`, which means that missing keypaths are not created.

This behavior of creating missing keypaths is available by setting the `json_compatibility_level` to `8.0`.

## Examples

The following examples illustrate the output when  `json_compatibility_level` is set to `7.8`.

```sql
SELECT JSON_SET_DOUBLE('{"alpha":1, "beta":2}', 'zeta', 'delta', 3);


```

```output

+---------------------------------------------------------------+
| JSON_SET_DOUBLE('{"alpha":1, "beta":2}', 'zeta', 'delta', 3) |
+---------------------------------------------------------------+
| {"alpha":1,"beta":2}                                          |
+---------------------------------------------------------------+
```

```sql
SELECT JSON_SET_DOUBLE('{"alpha":1, "beta":2}', 'zeta', 26);


```

```output

+------------------------------------------------------+
| JSON_SET_DOUBLE('{"alpha":1, "beta":2}', 'zeta', 26) |
+------------------------------------------------------+
| {"alpha":1,"beta":2,"zeta":26}                       |
+------------------------------------------------------+

```

```sql
SELECT JSON_SET_JSON('{"alpha":{"beta":{"gamma": 1}}}', 'alpha', 'beta', '[1,2,3]');


```

```output

+------------------------------------------------------------------------------+
| JSON_SET_JSON('{"alpha":{"beta":{"gamma": 1}}}', 'alpha', 'beta', '[1,2,3]') |  |
+------------------------------------------------------------------------------+
| {"alpha":{"beta":[1,2,3]}}                                                   |
+------------------------------------------------------------------------------+
```

```sql
SELECT JSON_SET_JSON('{"item1":null,"item2":[847,849]}', 'item1', 0, 'true') as item_numbers;


```

```output

+----------------------------------------+
| item_numbers                           |
+----------------------------------------+
| {"item1":null,"item2":[847,849]}.      |
+----------------------------------------+
```

```sql
SELECT JSON_SET_JSON('[2,{"item2":false}]', 0, 'item2', null) as item_numbers;


```

```output

+----------------------------+
| item_numbers               |
+----------------------------+
| [2,{"item2":false}]        |
+----------------------------+

```

```sql
SELECT JSON_SET_JSON('{"item1":{"912":913,"item2":0},"item3":[112,212,318],"item4":79}', 'item1', '0', 'x', 'true') as item_numbers;


```

```output

+------------------------------------------------------------------+
| item_numbers                                                     |
+------------------------------------------------------------------+
| {"item1":{"912":913,"item2":0},"item3":[112,212,318],"item4":79} |
+------------------------------------------------------------------+


```

The following examples illustrate the output when  `json_compatability_level` is set to `8.0`.

```sql
SELECT JSON_SET_JSON('{"item1":null,"item2":[847,849]}', 'item1', 0, 'true') as item_numbers;


```

```output

+----------------------------------------+
| item_numbers                           |
+----------------------------------------+
| {"item1":{"0":true},"item2":[847,849]} |
+----------------------------------------+
```

```sql
SELECT JSON_SET_JSON('[2,{"item2":false}]', 0, 'item2', null) as item_numbers;


```

```output

+-----------------------+
| item_numbers          |
+-----------------------+
| [{},{“item2”:false}]  |
+-----------------------+  
```

```sql
SELECT JSON_SET_JSON('{"item1":{"912":913,"item2":0},"item3":[112,212,318],"item4":79}', 'item1', '0', 'x', 'true') as item_numbers;


```

```output

+---------------------------------------------------------------------------------------------+
| item_numbers{“item1”:{“0":{“x”:true},“912":913,“item2”:0},“item3":[112,212,318],“item4”:79} |
+---------------------------------------------------------------------------------------------+

```

> **📝 Note**: A JSON or Javascript null value is distinct from SQL NULL.

## Related Topics

See the [Engine Variables Overview](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables.md) for information about engine variables.

***

Modified at: November 7, 2023

Source: [/db/v9.1/reference/sql-reference/json-functions/json-set-type/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-set-type/)

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