[] (get)

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

Use the [] operator for array indexing or object field access.

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 the object does not contain the specified key, the function returns NULL.

String keys apply only to OBJECT values. To retrieve an ARRAY element, use an integer index. Applying a string key to a non-object ANY value returns NULL.

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