类别：: 半结构化和结构化数据函数（数组/对象）

OBJECT_INSERT¶

返回一个由输入 OBJECT 值组成的 OBJECT 值，并插入了一个新键值对（或者用新值更新了现有的键）。

语法¶

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

实参¶

必填：

object: 插入新键值对或更新现有键值对的源 OBJECT 值。
key: 要插入至 OBJECT 值的新键或将更新值的现有键。指定键必须与 OBJECT 值中的所有现有键不同，除非将 updateFlag 设为 TRUE。
value: 与键关联的值。

可选：

updateFlag

布尔标志，当设置为 TRUE 时，指定输入值更新 OBJECT 值中的现有键值，而不是插入新键值对。

默认为 FALSE。

返回¶

该函数返回具有 OBJECT 数据类型的值。

使用说明¶

该函数支持 JSON null 值，但不支持 SQL NULL 值或键：
- 如果 key 是 NULL 以外的任何字符串，并且 value 是 JSON null（例如 PARSE_JSON('null')），则将键值对插入至返回的 OBJECT 值。
- 如果 key 或 value 为 SQL NULL，则返回的 OBJECT 值将省略键值对。
如果将可选的 updateFlag 实参设置为 TRUE，则现有输入 key 将更新为输入 value。如果省略 updateFlag 或将其设置为 FALSE，且该函数带有已存在于 OBJECT 值的输入键，则调用该函数会导致错误。。
如果 update 标志设置为 TRUE，但相应键并未存在于 OBJECT 值，则会添加键值对。

对于结构化的 OBJECT 值：

对于作为键的实参，您必须指定常量。

当 updateFlag 实参为 FALSE（插入新的键值对时）：

如果指定一个存在于 OBJECT 值的键，则会发生错误。

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

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

该函数返回一个结构化的 OBJECT 值。OBJECT 值的类型包括新插入的键。例如，假设您添加具有 zipcode 值 VARCHAR 的 94402 键：

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

+------------------------+---------------------------------------------------------------------+
| NEW_OBJECT             | TYPE                                                                |
|------------------------+---------------------------------------------------------------------|
| {                      | OBJECT(city VARCHAR, state VARCHAR, zip_code VARCHAR NOT NULL)[LOB] |
|   "city": "San Mateo", |                                                                     |
|   "state": "CA",       |                                                                     |
|   "zip_code": "94402"  |                                                                     |
| }                      |                                                                     |
+------------------------+---------------------------------------------------------------------+

插入值的类型决定了添加到 OBJECT 类型定义中的类型。在这种情况中，zipcode 的值是转换为 VARCHAR 的值，因此 zipcode 的类型为 VARCHAR。

当 updateFlag 实参为 TRUE 时（当您替换现有键值对时）：
- 如果指定的键并未存在于 OBJECT 值中，则会发生错误。
- 该函数返回相同类型的结构化 OBJECT 值。
- 将插入值的类型强制转换为现有键的类型。

示例¶

以下示例调用 OBJECT_INSERT 函数：

添加并更新键值对¶

示例使用下表：

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"  |
| }                |
+------------------+

将新键值对添加到 OBJECT 值¶

将第三个键值对插入到包含两个键值对的 OBJECT 值中：

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"  |
| }                |
+------------------+

将两个新的键值对插入到 OBJECT 值中，同时省略一个键值对：

d 由一个 JSON null 值组成。

e 由一个 SQL NULL 值组成，因此被省略。

f 由包含“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;

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

更新 OBJECT 值中的键值对¶

使用新值 ("valuex") 更新 OBJECT 值中的现有键值对 ("b": "value2")：

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"    |
| }                |
+------------------+

添加并更新嵌套 OBJECT 值¶

这些示例使用具有嵌套 OBJECT 值的下表：

CREATE OR REPLACE TABLE sample_nested_object (
  id INTEGER,
  nested_object OBJECT);

INSERT INTO sample_nested_object (id, nested_object)
  SELECT 1,
         OBJECT_CONSTRUCT(
           'outer_key1', OBJECT_CONSTRUCT('inner_key1A', 'example1', 'inner_key1B', 'example2'),
           'outer_key2', OBJECT_CONSTRUCT('inner_key2', 5)
         );

INSERT INTO sample_nested_object (id, nested_object)
  SELECT 2,
         OBJECT_CONSTRUCT(
           'outer_key1', OBJECT_CONSTRUCT('inner_key1A', 'example3', 'inner_key1B', 'example4'),
           'outer_key2', OBJECT_CONSTRUCT('inner_key2', 7)
         );

SELECT * FROM sample_nested_object;

+----+--------------------------------+
| ID | NESTED_OBJECT                  |
+----+--------------------------------+
| 1  | {                              |
|    |   "outer_key1": {              |
|    |     "inner_key1A": "example1", |
|    |     "inner_key1B": "example2"  |
|    |   },                           |
|    |   "outer_key2": {              |
|    |     "inner_key2": 5            |
|    |   }                            |
|    | }                              |
| 2  | {                              |
|    |   "outer_key1": {              |
|    |     "inner_key1A": "example3", |
|    |     "inner_key1B": "example4"  |
|    |   },                           |
|    |   "outer_key2": {              |
|    |     "inner_key2": 7            |
|    |   }                            |
|    | }                              |
+----+--------------------------------+

将新的嵌套键值对添加到嵌套 OBJECT 值¶

以下示例将新的嵌套键值对添加到表中的嵌套 OBJECT 值。它使用 CASE 表达式，为每行指定添加的键值对：

UPDATE sample_nested_object
  SET nested_object = OBJECT_INSERT(
    nested_object,
    'outer_key1',
     OBJECT_INSERT(
       nested_object:outer_key1,
       'inner_key1C',
       CASE
         WHEN id = 1 THEN 'added_value_1'
         WHEN id = 2 THEN 'added_value_2'
       END,
       TRUE
      ),
    TRUE);

SELECT * FROM sample_nested_object;

+----+------------------------------------+
| ID | NESTED_OBJECT                      |
|----+------------------------------------|
|  1 | {                                  |
|    |   "outer_key1": {                  |
|    |     "inner_key1A": "example1",     |
|    |     "inner_key1B": "example2",     |
|    |     "inner_key1C": "added_value_1" |
|    |   },                               |
|    |   "outer_key2": {                  |
|    |     "inner_key2": 5                |
|    |   }                                |
|    | }                                  |
|  2 | {                                  |
|    |   "outer_key1": {                  |
|    |     "inner_key1A": "example3",     |
|    |     "inner_key1B": "example4",     |
|    |     "inner_key1C": "added_value_2" |
|    |   },                               |
|    |   "outer_key2": {                  |
|    |     "inner_key2": 7                |
|    |   }                                |
|    | }                                  |
+----+------------------------------------+

更新嵌套 OBJECT 值中的键值对¶

以下示例将更新表中 OBJECT 值的嵌套键值对：

UPDATE sample_nested_object
  SET nested_object = OBJECT_INSERT(
    nested_object,
    'outer_key2',
    OBJECT_INSERT(
      nested_object:outer_key2,
      'inner_key2',
      CASE
        WHEN id = 1 THEN 6
        WHEN id = 2 THEN 8
      END,
      TRUE),
    TRUE);

SELECT * FROM sample_nested_object;

+----+------------------------------------+
| ID | NESTED_OBJECT                      |
|----+------------------------------------|
|  1 | {                                  |
|    |   "outer_key1": {                  |
|    |     "inner_key1A": "example1",     |
|    |     "inner_key1B": "example2",     |
|    |     "inner_key1C": "added_value_1" |
|    |   },                               |
|    |   "outer_key2": {                  |
|    |     "inner_key2": 6                |
|    |   }                                |
|    | }                                  |
|  2 | {                                  |
|    |   "outer_key1": {                  |
|    |     "inner_key1A": "example3",     |
|    |     "inner_key1B": "example4",     |
|    |     "inner_key1C": "added_value_2" |
|    |   },                               |
|    |   "outer_key2": {                  |
|    |     "inner_key2": 8                |
|    |   }                                |
|    | }                                  |
+----+------------------------------------+