JSON_SET

Updates existing values or inserts new values in a JSON document at specified paths.

Syntax

SELECT JSON_SET(json_column, path, value_to_set) FROM <multipartIdentifier>;

Arguments

json_column
The JSON column or expression to be modified.

path
The JSON path expression indicating where to set the value. If the path exists, the value is updated; if the path does not exist, a new key-value pair is added.

value_to_set
The value to be set at the specified path in the JSON document.

Return Value(s)

Returns a JSON document with the value set at the specified path. If the path exists, the existing value is updated; otherwise, a new key-value pair is added.

Behavior If a Key Exists

Function	Overwrite if already exists?	Create if does not exist?
`json_insert()`	No	Yes
`json_replace()`	Yes	No
`json_set()`	Yes	Yes

Examples

Update an existing value in a JSON object

-- Example: Update the 'status' key's value to 'active' in a JSON object
SELECT JSON_SET(json_data, '$.status', 'active') as updated_json
FROM cloud_services
WHERE service_id = 'service123';

-- Expected output:
-- |---------------------------------------------------------|
-- |                    updated_json                         |
-- |---------------------------------------------------------|
-- | {"name":"ServiceOne", "status":"active", "region":"US"} |
-- |---------------------------------------------------------|

This query updates the 'status' key to 'active' in the JSON document if the 'status' key exists. If 'status' does not exist in the document, it is added with the value 'active'.

Add a new key-value pair to a JSON object

-- Example: Add a new key 'lastChecked' with the current date to a JSON object
SELECT JSON_SET(json_data, '$.lastChecked', '2024-04-29') as updated_json
FROM cloud_services
WHERE service_id = 'service123';

/* expected output...
|-----------------------------------------------------------------------|
|                          updated_json                                 |
|-----------------------------------------------------------------------|
| {"name":"ServiceOne", "status":"pending", "lastChecked":"2024-04-29"} |
|-----------------------------------------------------------------------|
*/

This query adds a new key 'lastChecked' with the value '2024-04-29' to the JSON document if 'lastChecked' does not already exist. If it does, the existing value is updated to '2024-04-29'.

Syntax​

Arguments​

Return Value(s)​

Examples​

Update an existing value in a JSON object​

Add a new key-value pair to a JSON object​