Categories:

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

OBJECT_PICK

Returns a new OBJECT containing some of the key-value pairs from an existing object.

To identify the key-value pairs to include in the new object, pass in the keys as arguments, or pass in an array containing the keys.

If a specified key is not present in the input object, the key is ignored.

Syntax

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

OBJECT_PICK( <object>, <array> )
Copy

Arguments

object

The input object.

key1, key2

One or more keys identifying the key-value pairs that should be included in the returned object.

array

Array of keys identifying the key-value pairs that should be included in the returned object.

Returns

Returns a new OBJECT containing the specified key-value pairs.

Usage notes

For structured OBJECTs:

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

  • You cannot pass in an ARRAY of keys as the second argument. You must specify each key as a separate argument.

  • The function returns a structured OBJECT. The type of the OBJECT includes the keys in the order in which they are specified.

    For example, suppose that you select the state and city keys in that order:

    SELECT
  OBJECT_PICK(
    {'city':'San Mateo','state':'CA','zip_code':94402}::OBJECT(city VARCHAR,state VARCHAR,zip_code DOUBLE),
    'state',
    'city') AS new_object,
  SYSTEM$TYPEOF(new_object);
    Copy

    The function returns an OBJECT of the type OBJECT(state VARCHAR, city VARCHAR).

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

Examples

The following example calls OBJECT_PICK to create a new object that contains two of the three key-value pairs from an existing object:

SELECT OBJECT_PICK(
    OBJECT_CONSTRUCT(
        'a', 1,
        'b', 2,
        'c', 3
    ),
    'a', 'b'
) AS new_object;
+------------+
| NEW_OBJECT |
|------------|
| {          |
|   "a": 1,  |
|   "b": 2   |
| }          |
+------------+
Copy

In the example above, the keys are passed as arguments to OBJECT_PICK. You can also use an array to specify the keys, as shown below:

SELECT OBJECT_PICK(
    OBJECT_CONSTRUCT(
        'a', 1,
        'b', 2,
        'c', 3
    ),
    ARRAY_CONSTRUCT('a', 'b')
) AS new_object;
+------------+
| NEW_OBJECT |
|------------|
| {          |
|   "a": 1,  |
|   "b": 2   |
| }          |
+------------+
Copy
Language: English