Documentation Index
Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
Use this file to discover all available pages before exploring further.
Operators for Native JSON Values
The following operators are supported on JSON values.
Dot Operator .
Extract the nested JSON value with a given field name from a JSON object. If a nested value with the requested field name does not exist NULL is returned.
Syntax
Parameters
| Parameter | Description | Supported Input Types |
|---|
<json> | The JSON value from which the field should be extracted. | JSON |
<field_name> | A SQL identifier specifying the field name that should be extracted. | SQL identifier |
<field_name> can be double-quoted if necessary, e.g. in order to write <json>."key with spaces" or <json>."1". The dot operator can only be used to extract nested values from JSON objects, i.e. it returns a non-NULL result only if <json> is a JSON object that contains <field_name> as a key. If <json> is a JSON array the operator returns NULL unconditionally, even if <field_name> happens to contains a valid zero-based array index.
Return Type
JSON
- If the
<json> input value is NULL the operator will return NULL.
- If a field with the requested name does not exist the operator will return
NULL.
Subscript Operator []
Extract a field of a JSON value as another JSON value. Matches against both JSON object entries and JSON array entries in the input. If a field with the requested name or index does not exist NULL is returned.
Syntax
<json>[<field_name_or_array_index>]
Parameters
| Parameter | Description | Supported Input Types |
|---|
<json> | The JSON value from which the field should be extracted. | JSON |
<field_name_or_array_index> | A TEXT, INTEGER, or BIGINT value specifying the field name or array index that should be extracted. | TEXT, INTEGER, or BIGINT |
<json> is a JSON object that contains <field_name_or_array_index> as a key and if <json> is a JSON array and <field_name_or_array_index> contains a valid zero-based array index. The type of the <field_name_or_array_index> parameter has no effect on the query result. More specifically, when <json> contains a JSON object the parameter value is always converted to TEXT internally. Conversely, when <json> contains a JSON array the parameter value is always converted to BIGINT internally. If this conversion fails the operator will return NULL. Unlike the dot operator, the subscript operator accepts non-literal <field_name_or_array_index> parameters.
Return Type
JSON
- If the
<json> input value is NULL the operator will return NULL.
- If a field with the requested name or index does not exist the operator will return
NULL.
Examples
The following table is used in all examples:
CREATE TABLE t (col JSON);
INSERT INTO t VALUES ('
{
"a": [
42,
{
"b": {
"message": "hello world!",
"42": "the answer"
}
},
42
]
}
');
NULL return value in all cases:
SELECT col.non_existent FROM t; -- JSON object has no entry with key "non_existent"
SELECT col['non_existent'] FROM t; -- JSON object has no entry with key "non_existent"
SELECT col.a."1" FROM t; -- Dot operator cannot be used to access JSON array elements
SELECT col.a[3] FROM t; -- Index 3 is out of bounds for JSON array of length 3
SELECT col.a['3'] FROM t; -- Index 3 is out of bounds for JSON array of length 3
SELECT col.a['foo'] FROM t; -- col.a is a JSON array but "foo" cannot be converted to BIGINT
SELECT col.a FROM t;
SELECT col."a" FROM t;
SELECT col['a'] FROM t;
| ?column? (JSON) |
|---|
[42,{"b":{"message":"hello world!","42":"the answer"}},42] |
SELECT col.a[0] FROM t;
SELECT col.a['0'] FROM t;
SELECT col.a[1].b."42" FROM t;
SELECT col.a[1].b[42] FROM t;
SELECT col.a[1].b['42'] FROM t;
| ?column? (JSON) |
|---|
"the answer" |
<field_name_or_array_index> parameter to the subscript operator.
SELECT index, col.a[index] FROM t, generate_series(-1,3) g(index);
| index INTEGER | ?column? (JSON) |
|---|
-1 | NULL |
0 | '42' |
1 | '{"b":{"42":"the answer","message":"hello world!"}}' |
2 | '42' |
3 | NULL |
Operators for TEXT Values Storing JSON Strings
The following operators are supported on TEXT values storing JSON strings. Note that each invocation of these operators has to re-parse the input JSON string from scratch, making them substantially less efficient than the native JSON operators described above.
Operator ->>
Get value of a JSON field as text.
It never looks into nested JSON documents. Special characters like / in field names are escaped and not interpreted as JSON path separators.
Syntax
Parameters
| Parameter | Description | Supported input types |
|---|
<json> | The JSON document. | TEXT |
<field_name> | Name of the JSON field for which the value should be extracted. | TEXT |
Return Type
TEXT
- If any input values are
NULL, the function will return NULL.
- If the field name is not found in the JSON document, the function returns
NULL.
Examples
The following CREATE TABLE and INSERT statements set up the examples.
create table json_operators (s text);
insert into json_operators
values ('
{
"key": 123,
"key~with~tilde": 2,
"key/with/slash": 3,
"value": {
"dyid": 987,
"keywords" : ["insanely","fast","analytics"]
}
}
');
SELECT s->>'key' FROM json_operators;
SELECT s->>'key/with/slash' FROM json_operators;
~ and / in the field name when using the ->> operator.
Example
SELECT s->>'value' FROM json_operators;
| ?column? (TEXT) |
|---|
'{\n "dyid": 987,\n "keywords" : ["insanely","fast","analytics"]\n }\n' |
value as a TEXT value.
Example
SELECT s->>'value'->>'dyid' FROM json_operators;
->> operator retrieves the nested JSON document in text form. Applying the second ->> operator on the nested JSON document returns the value associated with the JSON field dyid. The operator returns this value as a string.
Example
SELECT s->>'value/dyid' FROM json_operators;
value/dyid. The ->> operator never looks into nested JSON documents.
