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

OBJECT_DELETE¶

返回一个对象，它包含输入（即源）对象的内容，并移除了一个或多个键。

语法¶

OBJECT_DELETE( <object>, <key1> [, <key2>, ... ] )

实参¶

object: 源对象。
key1、key2: 在返回的对象中要忽略的键。

返回¶

此函数返回 OBJECT 类型的值。

使用说明¶

对于结构化 OBJECTs：

对于作为键的实参，您必须指定常量。
如果指定的键不是 OBJECT 类型定义的一部分，则调用失败。例如，以下调用失败，因为 OBJECT 值不包含指定的键 zip_code：
```
SELECT OBJECT_DELETE( {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR), 'zip_code' );
```
```
093201 (23001): Function OBJECT_DELETE: expected structured object to contain field zip_code but it did not.
```

该函数返回一个结构化的 OBJECT 值。OBJECT 值的类型会排除已删除的键。例如，假设您移除了 city 键：

SELECT
  OBJECT_DELETE(
    {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR),
    'city'
  ) AS new_object,
  SYSTEM$TYPEOF(new_object);

该函数会返回 OBJECT(state VARCHAR) 类型的 OBJECT 值，其中不包含 city 键。

+-----------------+----------------------------+
| NEW_OBJECT      | SYSTEM$TYPEOF(NEW_OBJECT)  |
|-----------------+----------------------------|
| {               | OBJECT(state VARCHAR)[LOB] |
|   "state": "CA" |                            |
| }               |                            |
+-----------------+----------------------------+

如果该函数从 OBJECT 值中移除所有键，则该函数将返回 OBJECT() 类型的空结构化 OBJECT 值。
```
SELECT
  OBJECT_DELETE(
    {'state':'CA'}::OBJECT(state VARCHAR),
    'state'
  ) AS new_object,
  SYSTEM$TYPEOF(new_object);
```
```
+------------+---------------------------+
| NEW_OBJECT | SYSTEM$TYPEOF(NEW_OBJECT) |
|------------+---------------------------|
| {}         | OBJECT()[LOB]             |
+------------+---------------------------+
```
当结构化 OBJECT 值的类型包括键值对时，这些对的名称和类型将包含在类型的括号中（例如，OBJECT(city VARCHAR)）。由于空结构化 OBJECT 值不包含键值对，因此括号为空。

示例¶

此查询返回一个对象，从源对象中排除键 a 和 b：

SELECT OBJECT_DELETE(OBJECT_CONSTRUCT('a', 1, 'b', 2, 'c', 3), 'a', 'b') AS object_returned;

+-----------------+
| OBJECT_RETURNED |
|-----------------|
| {               |
|   "c": 3        |
| }               |
+-----------------+

创建表并插入包含 OBJECT 值的行。此示例在 INSERT 语句中使用 OBJECT 常量。

CREATE OR REPLACE TABLE object_delete_example (
  id INTEGER,
  ov OBJECT);

INSERT INTO object_delete_example (id, ov)
  SELECT
    1,
    {
      'employee_id': 1001,
      'employee_date_of_birth': '12-10-2003',
      'employee_contact':
        {
          'city': 'San Mateo',
          'state': 'CA',
          'phone': '800-555-0100'
        }
    };

INSERT INTO object_delete_example (id, ov)
  SELECT
    2,
    {
      'employee_id': 1002,
      'employee_date_of_birth': '01-01-1990',
      'employee_contact':
        {
          'city': 'Seattle',
          'state': 'WA',
          'phone': '800-555-0101'
        }
    };

查询表以查看数据：

SELECT * FROM object_delete_example;

+----+-------------------------------------------+
| ID | OV                                        |
|----+-------------------------------------------|
|  1 | {                                         |
|    |   "employee_contact": {                   |
|    |     "city": "San Mateo",                  |
|    |     "phone": "800-555-0100",              |
|    |     "state": "CA"                         |
|    |   },                                      |
|    |   "employee_date_of_birth": "12-10-2003", |
|    |   "employee_id": 1001                     |
|    | }                                         |
|  2 | {                                         |
|    |   "employee_contact": {                   |
|    |     "city": "Seattle",                    |
|    |     "phone": "800-555-0101",              |
|    |     "state": "WA"                         |
|    |   },                                      |
|    |   "employee_date_of_birth": "01-01-1990", |
|    |   "employee_id": 1002                     |
|    | }                                         |
+----+-------------------------------------------+

要从查询输出中删除 employee_date_of_birth 键，请执行以下查询：

SELECT id,
       OBJECT_DELETE(ov, 'employee_date_of_birth') AS contact_without_date_of_birth
  FROM object_delete_example;

+----+-------------------------------+
| ID | CONTACT_WITHOUT_DATE_OF_BIRTH |
|----+-------------------------------|
|  1 | {                             |
|    |   "employee_contact": {       |
|    |     "city": "San Mateo",      |
|    |     "phone": "800-555-0100",  |
|    |     "state": "CA"             |
|    |   },                          |
|    |   "employee_id": 1001         |
|    | }                             |
|  2 | {                             |
|    |   "employee_contact": {       |
|    |     "city": "Seattle",        |
|    |     "phone": "800-555-0101",  |
|    |     "state": "WA"             |
|    |   },                          |
|    |   "employee_id": 1002         |
|    | }                             |
+----+-------------------------------+

要查询 employee_contact 嵌套对象，从中移除 phone 键，并仅返回嵌套的内部键值对，请运行执行以下操作的查询：

SELECT id,
       OBJECT_DELETE(ov:"employee_contact", 'phone') AS contact_without_phone
  FROM object_delete_example;

+----+------------------------+
| ID | CONTACT_WITHOUT_PHONE  |
|----+------------------------|
|  1 | {                      |
|    |   "city": "San Mateo", |
|    |   "state": "CA"        |
|    | }                      |
|  2 | {                      |
|    |   "city": "Seattle",   |
|    |   "state": "WA"        |
|    | }                      |
+----+------------------------+

要查询 employee_contact 嵌套对象，从中移除 phone 键，并返回完整对象而不仅是嵌套的内部键值对，请运行执行以下操作的查询：

调用 OBJECT_INSERT 函数并指定第一个实参的 ov 列。该函数从每行中的整个对象开始。
在 OBJECT_INSERT 调用的第二个实参中，指定 employee_contact 作为要更新的现有键。
在 OBJECT_INSERT 调用的第三个参数中，调用 OBJECT_DELETE 函数以从嵌套对象中移除 phone 键。
在 OBJECT_INSERT 调用的最后一个实参中，指定 true 以将旧对象替换为新对象。

执行以下查询以执行这些操作：

SELECT id,
       OBJECT_INSERT(
         ov,
         'employee_contact',
         OBJECT_DELETE(
           ov:employee_contact,
           'phone'
         ),
         true
      ) AS full_object_without_phone
  FROM object_delete_example;

+----+-------------------------------------------+
| ID | FULL_OBJECT_WITHOUT_PHONE                 |
|----+-------------------------------------------|
|  1 | {                                         |
|    |   "employee_contact": {                   |
|    |     "city": "San Mateo",                  |
|    |     "state": "CA"                         |
|    |   },                                      |
|    |   "employee_date_of_birth": "12-10-2003", |
|    |   "employee_id": 1001                     |
|    | }                                         |
|  2 | {                                         |
|    |   "employee_contact": {                   |
|    |     "city": "Seattle",                    |
|    |     "state": "WA"                         |
|    |   },                                      |
|    |   "employee_date_of_birth": "01-01-1990", |
|    |   "employee_id": 1002                     |
|    | }                                         |
+----+-------------------------------------------+