Semi-structured and structured data functions (Array/Object)
Returns an OBJECT value consisting of the input OBJECT value 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 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.
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 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, state VARCHAR, 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.
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;
Copy
+------------------+
| OBJECT_COLUMN |
|------------------|
| { |
| "a": "value1", |
| "b": "value2" |
| } |
+------------------+
Add a new key-value pair to an OBJECT value¶
Insert a third key-value pair into an OBJECT value that has two key-value pairs:
UPDATE object_insert_examples SET object_column = OBJECT_INSERT(object_column, 'c', 'value3'); SELECT * FROM object_insert_examples;
Copy
+------------------+
| OBJECT_COLUMN |
|------------------|
| { |
| "a": "value1", |
| "b": "value2", |
| "c": "value3" |
| } |
+------------------+
Insert two new key-value pairs into the OBJECT value, while omitting one key-value pair:
dconsists of a JSON null value.
econsists of a SQL NULL value and is, therefore, omitted.
fconsists of a string containing “null”.
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;
Copy
+------------------+
| OBJECT_COLUMN |
|------------------|
| { |
| "a": "value1", |
| "b": "value2", |
| "c": "value3", |
| "d": null, |
| "f": "null" |
| } |
+------------------+
Updating a key-value pair in an OBJECT value¶
Update an existing key-value pair ("b": "value2") in the OBJECT value with a new value ("valuex"):
UPDATE object_insert_examples SET object_column = OBJECT_INSERT(object_column, 'b', 'valuex', TRUE); SELECT * FROM object_insert_examples;
Copy
+------------------+
| OBJECT_COLUMN |
|------------------|
| { |
| "a": "value1", |
| "b": "valuex", |
| "c": "value3", |
| "d": null, |
| "f": "null" |
| } |
+------------------+
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4