GET¶

Syntax¶

ARRAY (or VARIANT containing an ARRAY)

GET( <array> , <index> )

GET( <variant> , <index> )

OBJECT (or VARIANT containing an OBJECT)

GET( <object> , <field_name> )

GET( <variant> , <field_name> )

Arguments¶

object

An expression that evaluates to an OBJECT that contains key-value pairs.

field_name

An expression that evaluates to a VARCHAR. This specifies the key in a key-value pair for which you want to retrieve the value.

If the OBJECT does not contain the specified key, this function returns NULL.

The field_name should not be an empty string.

array

An expression that evaluates to an ARRAY.

index

An expression that evaluates to an INTEGER. This specifies the position of the element to retrieve from the ARRAY. The position is 0-based, not 1-based.

If the index points outside of the array boundaries, or if the indexed element does not exist (in a sparse array), then this function returns NULL.

variant

An expression that evaluates to a VARIANT that contains either an ARRAY or an OBJECT.

Returns¶

The data type of the returned value is VARIANT.

The returned value is the specified element of the ARRAY, or the value that corresponds to the specified key of a key-value pair in the OBJECT. The data type is VARIANT because:

• In an ARRAY, each element is of type VARIANT.

• In an OBJECT, the value in each key-value pair is of type VARIANT.

Usage Notes¶

• GET applies case-sensitive matching to field_name. For case-insensitive matching, use GET_IGNORE_CASE.

• If the first parameter is of type VARIANT:

  • If the second parameter is of type VARCHAR (e.g. a field_name), the function returns NULL if variant does not contain an OBJECT.

  • If the second parameter is of type INTEGER (e.g. an index), the function returns NULL if variant does not contain an ARRAY.

Examples¶

Create a table with sample data:

CREATE TABLE vartab (a ARRAY, o OBJECT, v VARIANT);
INSERT INTO vartab (a, o, v) 
  SELECT
    ARRAY_CONSTRUCT(2.71, 3.14),
    OBJECT_CONSTRUCT('Ukraine', 'Kyiv'::VARIANT, 
                     'France',  'Paris'::VARIANT),
    TO_VARIANT(OBJECT_CONSTRUCT('weatherStationID', 42::VARIANT,
                     'timestamp', '2022-03-07 14:00'::TIMESTAMP_LTZ::VARIANT,
                     'temperature', 31.5::VARIANT,
                     'sensorType', 'indoor'::VARIANT))
    ;

Copy

SELECT a, o, v FROM vartab;
+---------+----------------------+-------------------------------------------------+
| A       | O                    | V                                               |
|---------+----------------------+-------------------------------------------------|
| [       | {                    | {                                               |
|   2.71, |   "France": "Paris", |   "sensorType": "indoor",                       |
|   3.14  |   "Ukraine": "Kyiv"  |   "temperature": 31.5,                          |
| ]       | }                    |   "timestamp": "2022-03-07 14:00:00.000 -0800", |
|         |                      |   "weatherStationID": 42                        |
|         |                      | }                                               |
+---------+----------------------+-------------------------------------------------+

Copy

Extract the first element of an ARRAY:

SELECT GET(a, 0) FROM vartab;
+-----------+
| GET(A, 0) |
|-----------|
| 2.71      |
+-----------+

Copy

Given the name of a country, extract the name of the capital city of that country from an OBJECT containing country names and capital cities:

SELECT GET(o, 'Ukraine') FROM vartab;
+-------------------+
| GET(O, 'UKRAINE') |
|-------------------|
| "Kyiv"            |
+-------------------+

Copy

Extract the temperature from a VARIANT that contains an OBJECT:

SELECT GET(v, 'temperature') FROM vartab;
+-----------------------+
| GET(V, 'TEMPERATURE') |
|-----------------------|
| 31.5                  |
+-----------------------+

Copy

For more detailed examples, see Querying Semi-structured Data.

For examples of using GET with XMLGET, see the Examples and Usage Notes sections in XMLGET.