- Overview
- Data types
- Commands
- Functions
- Overview
- Aggregate
- Conditional expression
- Conversion
- Date and time
- Numeric
- String
- Semi-structured data
[] (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"] |
+-----------------------+