- 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> ] )
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 andvalue
is a JSON NULL (e.g.PARSE_JSON('NULL')
), the key-value pair is inserted into the returned object.If either
key
orvalue
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 inputkey
is updated to the inputvalue
. IfupdateFlag
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 | } | ----------------------------------------------------+
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" | } | -----------------------+
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" | } | ------------------------+