[] (get)

Extracts a value from a semi-structured value that contains an array or object. The return value is of ANY type.

The [] operator provides alternative syntax for index or field get, or array map on key-value pairs.

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

Syntax

<data>[ <index> ]
<data>[ <field_name> ]

Arguments

<data>

An expression that evaluates to a semi-structured value that contains either an ARRAY or an OBJECT.

<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 the array boundaries, this function returns NULL.

<field_name>

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

<field_name> must not be an empty string.

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

If <data> is an array, the function iterates over the array elements and applies the get logic.

Returns

This function returns a semi-structured value.

Examples

Extract the first element of an ARRAY:

CREATE TABLE vartab (a any, o any, v any, s any);

VALUES (
    [2.71, 3.14],
    {"France": 'Paris', "Germany": 'Berlin'},
    {"sensorType": 'indoor', "temperature": 31.5, "timestamp": '2022-03-07T14:00:00.000-0800', "weatherStationID": 42},
    [{"name": "sensor1", "value": 23.5}, {"name": "sensor2", "value": 19.0}]
)
INSERT INTO vartab;
FROM vartab;
+-------------+---------------------------------------+------------------------------------------------+------------------------+
| a           | o                                     | v                                              | s                      |
+-------------+---------------------------------------+------------------------------------------------+------------------------+
| [2.71,3.14] | {"France":"Paris","Germany":"Berlin"} | {                                              | [                      |
|             |                                       |   "sensorType": "indoor",                      |   {                    |
|             |                                       |   "temperature": 31.5,                         |     "name": "sensor1", |
|             |                                       |   "timestamp": "2022-03-07T14:00:00.000-0800", |     "value": 23.5      |
|             |                                       |   "weatherStationID": 42                       |   },                   |
|             |                                       | }                                              |   {                    |
|             |                                       |                                                |     "name": "sensor2", |
|             |                                       |                                                |     "value": 19.0      |
|             |                                       |                                                |   }                    |
|             |                                       |                                                | ]                      |
+-------------+---------------------------------------+------------------------------------------------+------------------------+

Extract the first element of an ARRAY:

FROM vartab SELECT a[0];
+------+
| 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:

FROM vartab SELECT o['Germany'];
+--------------+
| o['Germany'] |
+--------------+
| "Berlin"     |
+--------------+

Extract the temperature from a semi-structured value that contains an OBJECT:

FROM vartab SELECT v['temperature'];
+------------------+
| v['temperature'] |
+------------------+
| 31.5             |
+------------------+

Extract the value of the name field from each element in an ARRAY of OBJECTs:

FROM vartab SELECT s['name'];
+-----------------------+
| s['name']             |
+-----------------------+
| ["sensor1","sensor2"] |
+-----------------------+