Categories:

Semi-structured and Structured Data Functions (Array/Object)

OBJECT_DELETE¶

Returns an object containing the contents of the input (i.e.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.

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¶

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

-------------------------------------------------------------------+
 OBJECT_DELETE(OBJECT_CONSTRUCT('A', 1, 'B', 2, 'C', 3), 'A', 'B') |
-------------------------------------------------------------------+
 {                                                                 |
   "c": 3                                                          |
 }                                                                 |
-------------------------------------------------------------------+
Copy