OBJECT_INSERT¶

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

• For structured OBJECTs:

  • For the arguments that are keys, you must specify constants.

  • When the updateFlag argument is FALSE (when you are inserting a new key-value pair):

    • If you specify a key that already exists in the OBJECT, an error occurs.

      SELECT OBJECT_INSERT(
        {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR),
        'city',
        'San Jose',
        false
      );
      

      Copy

      093202 (23001): Function OBJECT_INSERT:
        expected structured object to not contain field city but it did.
      

    • The function returns a structured OBJECT. The type of the OBJECT includes the newly inserted key. For example, suppose that you add the zipcode key with the DOUBLE value 94402:

      SELECT
        OBJECT_INSERT(
          {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR),
          'zip_code',
          94402::DOUBLE,
          false
        ) AS new_object,
        SYSTEM$TYPEOF(new_object);
      

      Copy

      The function returns an OBJECT of the type OBJECT(city VARCHAR, state VARCHAR, zipcode DOUBLE):

      +-------------------------------------+---------------------------------------------------------------------------------------+
      | NEW_OBJECT                          | SYSTEM$TYPEOF(NEW_OBJECT)                                                             |
      |-------------------------------------+---------------------------------------------------------------------------------------|
      | {                                   | OBJECT(city VARCHAR(16777216), state VARCHAR(16777216), zip_code FLOAT NOT NULL)[LOB] |
      |   "city": "San Mateo",              |                                                                                       |
      |   "state": "CA",                    |                                                                                       |
      |   "zip_code": 9.440200000000000e+04 |                                                                                       |
      | }                                   |                                                                                       |
      +-------------------------------------+---------------------------------------------------------------------------------------+
      

      The type of the inserted value determines the type added to the OBJECT type definition. In this case, the value for zipcode is a value cast to a DOUBLE, so the type of zipcode is DOUBLE.

  • When the updateFlag argument is TRUE (when you are replacing an existing key-value pair):

    • If you specify a key that does not exist in the OBJECT, an error occurs.

    • The function returns a structured OBJECT of the same type.

    • The type of the inserted value is coerced to the type of the existing key.

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