Categories:: Semi-structured and structured data functions (Array/Object)

OBJECT_DELETE¶

Returns an object containing the contents of the input (that is, source) object with one or more keys removed.

Syntax¶

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

Copy

Arguments¶

object: The source object.
key1, key2: Key to be omitted from the returned object.

Returns¶

This function returns a value of type OBJECT.

Usage notes¶

For structured OBJECTs:

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

If the specified key is not part of the OBJECT type definition, the call fails. For example, the following call fails because the OBJECT does not contain the specified key zip_code:

SELECT OBJECT_DELETE( {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR), 'zip_code' );

Copy

093201 (23001): Function OBJECT_DELETE: expected structured object to contain field zip_code but it did not.

The function returns a structured OBJECT. The type of the OBJECT excludes the deleted key. For example, suppose that you remove the city key:

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

Copy

The function returns an OBJECT of the type OBJECT(state VARCHAR), which does not include the city key.

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

If the function removes all keys from the object, the function returns an empty structured OBJECT of the type OBJECT().
```
SELECT
  OBJECT_DELETE(
    {'state':'CA'}::OBJECT(state VARCHAR),
    'state'
  ) AS new_object,
  SYSTEM$TYPEOF(new_object);
```
Copy
```
+------------+---------------------------+
| NEW_OBJECT | SYSTEM$TYPEOF(NEW_OBJECT) |
|------------+---------------------------|
| {}         | OBJECT()[LOB]             |
+------------+---------------------------+
```
When the type of a structured OBJECT includes key-value pairs, the names and types of those pairs are included in parentheses in the type (for example, OBJECT(city VARCHAR)). Because an empty structured OBJECT contains no key-value pairs, the parentheses are empty.

Examples¶

This query returns an object that excludes the keys a and b from the source object:

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

Copy

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

Create a table and insert rows with OBJECT values. This example uses OBJECT constants in the INSERT statements.

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'
        }
    };

Copy

Query the table to see the data:

SELECT * FROM object_delete_example;

Copy

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

To delete the employee_date_of_birth key from the query output, execute the following query:

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

Copy

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

To query the employee_contact nested object and remove the phone key from it, execute the following query:

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

Copy

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