OBJECT_INSERT¶

Syntax¶

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

Arguments¶

Required:

object

The source OBJECT value into which the new key-value pair is inserted or in which an existing key-value pair is updated.

key

The new key to be inserted into the OBJECT value or an existing key whose value is being updated. The specified key must be different from all existing keys in the OBJECT value, unless updateFlag is set to TRUE.

value

The value associated with the key.

Optional:

updateFlag

A Boolean flag that, when set to TRUE, specifies that the input value updates the value of an existing key in the OBJECT value, rather than inserting a new key-value pair.

The default is FALSE.

Returns¶

This function returns a value that has the OBJECT data type.

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 (for example, PARSE_JSON('null')), the key-value pair is inserted into the returned OBJECT value.

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

• 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 value results in an error.

• If the update flag is set to TRUE, but the corresponding key doesn’t already exist in the OBJECT value, then the key-value pair is added.

• For structured OBJECT values:

  • 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 value, 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 value. The type of the OBJECT value includes the newly inserted key. For example, suppose that you add the zipcode key with the FLOAT value 94402:

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

      Copy

      +-------------------------------------+---------------------------------------------------------------------------------------+
      | NEW_OBJECT                          | TYPE                                                                                  |
      |-------------------------------------+---------------------------------------------------------------------------------------|
      | {                                   | 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 FLOAT, so the type of zipcode is FLOAT.

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

    • If you specify a key that doesn’t exist in the OBJECT value, an error occurs.

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

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

Examples¶

The examples use the following table:

CREATE OR REPLACE TABLE object_insert_examples (object_column OBJECT);

INSERT INTO object_insert_examples (object_column)
  SELECT OBJECT_CONSTRUCT('a', 'value1', 'b', 'value2');

SELECT * FROM object_insert_examples;

+------------------+
| OBJECT_COLUMN    |
|------------------|
| {                |
|   "a": "value1", |
|   "b": "value2"  |
| }                |
+------------------+

UPDATE object_insert_examples
  SET object_column = OBJECT_INSERT(object_column, 'c', 'value3');

SELECT * FROM object_insert_examples;

+------------------+
| OBJECT_COLUMN    |
|------------------|
| {                |
|   "a": "value1", |
|   "b": "value2", |
|   "c": "value3"  |
| }                |
+------------------+

UPDATE object_insert_examples
  SET object_column = OBJECT_INSERT(object_column, 'd', PARSE_JSON('null'));

UPDATE object_insert_examples
  SET object_column = OBJECT_INSERT(object_column, 'e', NULL);

UPDATE object_insert_examples
  SET object_column = OBJECT_INSERT(object_column, 'f', 'null');

SELECT * FROM object_insert_examples;

+------------------+
| OBJECT_COLUMN    |
|------------------|
| {                |
|   "a": "value1", |
|   "b": "value2", |
|   "c": "value3", |
|   "d": null,     |
|   "f": "null"    |
| }                |
+------------------+

UPDATE object_insert_examples
  SET object_column = OBJECT_INSERT(object_column, 'b', 'valuex', TRUE);

SELECT * FROM object_insert_examples;

+------------------+
| OBJECT_COLUMN    |
|------------------|
| {                |
|   "a": "value1", |
|   "b": "valuex", |
|   "c": "value3", |
|   "d": null,     |
|   "f": "null"    |
| }                |
+------------------+