Categories:

Semi-structured Data Functions (Array/Object)

OBJECT_INSERT

Returns an object consisting of the input object with a new key-value pair inserted (or an existing key updated with a new value).

Syntax

OBJECT_INSERT( <object> , <key> , <value> [ , <updateFlag> ] )
Copy

Arguments

Required:

object

The source object into which the new key-value pair is inserted.

key

The new key to be inserted into the object. Must be different from all existing keys in the object, unless updateFlag is set to TRUE.

value

The value associated with the key.

Optional:

updateFlag

Boolean flag that, when set to TRUE, specifies the input value is used to update/overwrite an existing key in the object, rather than inserting a new key-value pair.

The default is FALSE.

Usage Notes

  • The function supports JSON NULL values, but not SQL NULL values or keys:

    • If key is any string other than NULL and value is a JSON NULL (e.g. PARSE_JSON('NULL')), the key-value pair is inserted into the returned object.

    • If either key or value is a SQL NULL, the key-value pair is omitted from the returned object.

  • If the optional updateFlag argument is set to TRUE, the existing input key is updated to the input value. If updateFlag is omitted or set to FALSE, calling this function with an input key that already exists in the object results in an error.

  • If the update flag is set to true, but the corresponding key does not already exist in the object, then the key/value pair is added.

Examples

Insert a third key-value pair into an object containing two key-value pairs:

SELECT OBJECT_INSERT(OBJECT_CONSTRUCT('a',1,'b',2),'c',3);

----------------------------------------------------+
 OBJECT_INSERT(OBJECT_CONSTRUCT('A',1,'B',2),'C',3) |
----------------------------------------------------+
 {                                                  |
   "a": 1,                                          |
   "b": 2,                                          |
   "c": 3                                           |
 }                                                  |
----------------------------------------------------+
Copy

Insert two new key-value pairs, while omitting one key-value pair, into an empty object:

  • Key_One consists of a JSON NULL value.

  • Key_Two consists of a SQL NULL value and is, therefore, omitted.

  • Key_Three consists of a string containing “null”.

SELECT
  OBJECT_INSERT(OBJECT_INSERT(OBJECT_INSERT(OBJECT_CONSTRUCT(), 'Key_One', PARSE_JSON('NULL')), 'Key_Two', NULL), 'Key_Three', 'null')
  AS obj;

-----------------------+
          OBJ          |
-----------------------+
 {                     |
   "Key_One": null,    |
   "Key_Three": "null" |
 }                     |
-----------------------+
Copy

Update an existing key-value pair ("k1": 100) with a new value ("string-value"):

SELECT OBJECT_INSERT(OBJECT_INSERT(OBJECT_CONSTRUCT(),'k1', 100),'k1','string-value', TRUE) AS obj;

------------------------+
          OBJ           |
------------------------+
 {                      |
   "k1": "string-value" |
 }                      |
------------------------+
Copy
Language: English