Categories:

Semi-structured Data Functions (Extraction)

GET

Extracts a value from an ARRAY or an OBJECT (or a VARIANT that contains an ARRAY or OBJECT).

The function returns NULL if either of the arguments is NULL.

Note that this function should not be confused with the GET DML command.

See also:

GET_IGNORE_CASE , GET_PATH , :

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))
    ;

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

Extract the first element of an ARRAY:

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

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

Extract the temperature from a VARIANT that contains an OBJECT:

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

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.

⇧
Back to top