Functions for JSON

json_array_length()
json_contains()
json_exists()
json_extract()
json_extract_string()
json_group_array()
json_group_object()
json_keys()
json_transform()
json_transform_strict()
json_valid()
json_value()
json()
read_json()

Functions for working with .json extension files and with data type JSON.

Path inside JSON structure

Many functions for JSON use the path inside the JSON structure as one of the arguments. The path can be specified in either of two notations according to the following standards:

JSONPath
- $.key1.key2 — accessing the value of key key2
- $.key1.key2[i] — accessing the -th element of the list in the value of the key2 key
JSON Pointer
- /key1/key2 — accessing the value of key key2
- /key1/key2/i — accessing the -th element of the list at the value of key key2

The numbering of list items in JSON structure starts with 0.

The examples below use the JSON structure from this example:

CREATE TABLE js_table(js_data JSON);

INSERT INTO js_table VALUES
('{
  "first_name": "John",
  "last_name": "Smith",
  "is_alive": true,
  "age": 27,
  "address": {
    "street_address": "21 2nd Street",
    "city": "New York",
    "state": "NY",
    "postal_code": "10021-3100"
  },
  "phone_numbers": [
    {
      "type": "home",
      "number": "212 555-1234"
    },
    {
      "type": "office",
      "number": "646 555-4567"
    }
  ],
  "children": [
    "Catherine",
    "Thomas",
    "Trevor"
  ],
  "spouse": null
}');

`json_array_length()`

Description	Returns the number of elements in the array at the specified path in JSON, or `0` if the specified path is not an array.
Usage	`json_array_length(json[, path])`

If a list of paths is given in the second argument, the result is a list of array lengths on the specified paths.

See examples

SELECT
    json_array_length(js_data, '$.phone_numbers')                 AS phone_numbers,
    json_array_length(js_data, '$.children')                      AS children,
    json_array_length(js_data, ['$.phone_numbers', '$.children']) AS lists,
    json_array_length(js_data)                                    AS js_data,
    json_array_length(js_data, '$.first_name')                    AS first_name,
FROM js_table;

+---------------+----------+-------+---------+------------+
| phone_numbers | children | lists | js_data | first_name |
+---------------+----------+-------+---------+------------+
| 2             | 3        | {2,3} | 0       | 0          |
+---------------+----------+-------+---------+------------+

`json_contains()`

Description	Checks if the JSON structure contains the JSON substructure specified in the second argument.
Usage	`json_contains(json, json)`

Both arguments must be of type JSON. The second argument can be a numeric value or a text string, and the text string must be enclosed in double quotes.

See examples

SELECT
    json_contains(js_data, '27')                      AS result_1,
    json_contains(js_data, '"John"')                  AS result_2,
    json_contains(js_data, '{"first_name": "John"}')  AS result_3,
    json_contains(js_data, '{"first_name": "Smith"}') AS result_4, -- false expected
FROM js_table;

+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 |
+----------+----------+----------+----------+
| true     | true     | true     | false    |
+----------+----------+----------+----------+

`json_exists()`

Description	Checks if the JSON structure contains the specified path.
Usage	`json_exists(json, path)`

Returns BOOL or an array of BOOL[] for cases where path points to list items in the JSON structure.

See examples

SELECT
    json_exists(js_data, 'first_name')               AS result_1,
    json_exists(js_data, '$.first_name')             AS result_2,
    json_exists(js_data, '/first_name')              AS result_3,
    json_exists(js_data, '$.address.street_address') AS result_4,
    json_exists(js_data, '/address/street_address')  AS result_5,
    json_exists(js_data, '$.street_address')         AS result_6, -- false expected
    json_exists(js_data, '$.phone_numbers[*].type')  AS result_7,
    json_exists(js_data, '$..street_address')        AS result_8,
FROM js_table;

+----------+----------+----------+----------+----------+----------+-------------+----------+
| result_1 | result_2 | result_3 | result_4 | result_5 | result_6 | result_7    | result_8 |
+----------+----------+----------+----------+----------+----------+-------------+----------+
| true     | true     | true     | true     | true     | false    | {True,True} | {True}   |
+----------+----------+----------+----------+----------+----------+-------------+----------+

`json_extract()`

Description	Extracts data from a JSON structure at the specified path. Returns the data as `JSON`.
Usage	`json_extract(json, path)` or `json_extract(json, [path1, path2, {…])`.
Aliases	`json_extract_path`.

If a list of paths is given as the second argument, returns a list of values.

See examples

SELECT
    json_extract(js_data, 'first_name')                AS first_name,
    json_extract(js_data, ['first_name', 'last_name']) AS all_names,
    json_extract(js_data, '$.address.street_address')  AS street_address,
    json_extract(js_data, '$.phone_numbers[*].number') AS phone_numbers,
    json_extract(js_data, '$.children[0]')             AS child_1,
FROM js_table;

+------------+------------------+----------------+---------------------------------+-----------+
| first_name | all_names        | street_address | phone_numbers                   | child_1   |
+------------+------------------+----------------+---------------------------------+-----------+
| John       | {"John","Smith"} | 21 2nd Street  | {"212 555-1234","646 555-4567"} | Catherine |
+------------+------------------+----------------+---------------------------------+-----------+

`json_extract_string()`

Description	Extracts data from a JSON structure at the specified path. Returns the data in `VARCHAR` form.
Usage	`json_extract_string(json, path)` or `json_extract_string(json, [path1, path2, ...])`.
Aliases	`json_extract_path_text`.

If a list of paths is given as the second argument, returns a list of values.

See examples

SELECT
    json_extract_string(js_data, 'first_name')                AS first_name,
    json_extract_string(js_data, ['first_name', 'last_name']) AS all_names,
    json_extract_string(js_data, '$.address.street_address')  AS street_address,
    json_extract_string(js_data, '$.phone_numbers[*].number') AS phone_numbers,
    json_extract_string(js_data, '$.children[0]')             AS child_1,
FROM js_table;

+------------+--------------+----------------+-----------------------------+-----------+
| first_name | all_names    | street_address | phone_numbers               | child_1   |
+------------+--------------+----------------+-----------------------------+-----------+
| John       | {John,Smith} | 21 2nd Street  | {212 555-1234,646 555-4567} | Catherine |
+------------+--------------+----------------+-----------------------------+-----------+

`json_group_array()`

Description	Returns a JSON list containing all the values of a column.
Usage	`json_group_array(argument)`

The function changes the cardinality of the data.

See examples

CREATE TABLE example (name VARCHAR);
INSERT INTO example VALUES ('Tengri'), ('TNGRi');

SELECT json_group_array(name) AS tengti_names
FROM example;

+--------------------+
| tengti_names       |
+--------------------+
| ["Tengri","TNGRi"] |
+--------------------+

`json_group_object()`

Description	Returns a JSON structure containing all `key`-`value` pairs from the columns specified in the arguments.
Usage	`json_group_object(argument1, argument2)`

The function modifies the cardinality of the data.

See examples

CREATE TABLE example (name VARCHAR, letters_num BIGINT);
INSERT INTO example VALUES
    ('Tengri', 6),
    ('TNGRi', 5);

SELECT json_group_object(name, letters_num) AS result
FROM example;

+------------------------+
| result                 |
+------------------------+
| {"Tengri":6,"TNGRi":5} |
+------------------------+

`json_keys()`

Description	Returns all keys from the specified JSON structure as `VARCHAR[]`.
Usage	`json_keys(json[, path])`

If path is specified in the second argument, returns the keys of the JSON structure at the specified path. If a list of paths is specified, the result is a list of lists of keys.

See examples

SELECT
    json_keys(js_data) AS all_keys,
FROM js_table;

+---------------------------------------------------------------------------+
| all_keys                                                                  |
+---------------------------------------------------------------------------+
| {first_name,last_name,is_alive,age,address,phone_numbers,children,spouse} |
+---------------------------------------------------------------------------+

SELECT
    json_keys(js_data, '$.address') AS address_keys,
FROM js_table;

+-----------------------------------------+
| address_keys                            |
+-----------------------------------------+
| {street_address,city,state,postal_code} |
+-----------------------------------------+

SELECT
    json_keys(js_data, ['$.address',
                        '$.phone_numbers[0]'])
    AS address_and_phone_keys,
FROM js_table;

+-------------------------------------------------------------------------+
| address_and_phone_keys                                                  |
+-------------------------------------------------------------------------+
| {['street_address', 'city', 'state', 'postal_code'],['type', 'number']} |
+-------------------------------------------------------------------------+

`json_transform()`

Description	Transforms the JSON structure according to the specified structure.
Usage	`json_transform(json, string)`
Aliases	`from_json()`

The target structure is specified as a text string in the second argument.

In cases of missing values in the source JSON structure, NULL values are put into the result.

In cases of impossibility to convert data types in the source structure to the specified ones, NULL value is set.

See examples

CREATE TABLE example (js_data JSON);
INSERT INTO example VALUES
    ('{
        "first_name": "John",
        "last_name": "Smith",
        "age": 27
      }'),
    ('{
        "first_name": "John",
        "is_alive": true,
        "age": 28
      }');

SELECT
    json_transform(js_data,
        '{
        "first_name": "VARCHAR",
        "last_name": "VARCHAR",
        "is_alive": "BOOL",
        "age": "BIGINT"
        }')
    AS result
FROM example;

+---------------------------------------------------------------------------+
| result                                                                    |
+---------------------------------------------------------------------------+
| {"first_name": "John", "last_name": "Smith", "is_alive": null, "age": 27} |
+---------------------------------------------------------------------------+
| {"first_name": "John", "last_name": null, "is_alive": true, "age": 28}    |
+---------------------------------------------------------------------------+

`json_transform_strict()`

Description	Transforms a JSON structure to match the specified structure. Issues an error if structures or types do not match.
Usage	`json_transform_strict(json, string)`.
Aliases	`from_json_strict()`

The target structure is specified as a text string in the second argument.

In cases where there are no values from the specified structure in the source JSON structure, produces an error.

If it is impossible to convert data types in the source structure to the specified ones, it generates an error.

See examples

CREATE TABLE example (js_data JSON);
INSERT INTO example VALUES
    ('{
        "first_name": "John",
        "last_name": "Smith",
        "age": 27
      }'),
    ('{
        "first_name": "John",
        "is_alive": true,
        "age": 28
      }');
SELECT
    json_transform_strict(js_data,
        '{"first_name": "VARCHAR", "age": "BIGINT"}')
    AS result
FROM example;

+-----------------------------------+
| result                            |
+-----------------------------------+
| {"first_name": "John", "age": 27} |
+-----------------------------------+
| {"first_name": "John", "age": 28} |
+-----------------------------------+

CREATE TABLE example (js_data JSON);
INSERT INTO example VALUES
    ('{
        "first_name": "John",
        "last_name": "Smith",
        "age": 27
      }'),
    ('{
        "first_name": "John",
        "is_alive": true,
        "age": 28
      }');
SELECT
    json_transform_strict(js_data,
        '{"first_name": "BIGINT", "age": "BIGINT"}') -- error expected
    AS result
FROM example;

ERROR: InvalidInputException: Invalid Input Error:
Failed to cast value to numerical: "John"

CREATE TABLE example (js_data JSON);
INSERT INTO example VALUES
    ('{
        "first_name": "John",
        "last_name": "Smith",
        "age": 27
      }'),
    ('{
        "first_name": "John",
        "is_alive": true,
        "age": 28
      }');

SELECT
    json_transform_strict(js_data,
        '{"first_name": "VARCHAR", "last_name": "VARCHAR"}') -- error expected
    AS result
FROM example;

ERROR: InvalidInputException: Invalid Input Error:
Object {"first_name":"John","is_alive":true,"age":28} does not have key "last_name"

`json_valid()`

Description	Checks if the argument is a valid JSON structure.
Usage	`json_valid(json)`

See examples

SELECT
    json_valid(js_data)                  AS result_1,
    json_valid('{"first_name": "John"}') AS result_2,
    json_valid('{"first_name"}')         AS result_3, -- false expected
FROM js_table;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| true     | true     | false    |
+----------+----------+----------+

`json_value()`

Description	Retrieves values from a JSON structure at the specified path.
Usage	`json_value(json, path)`

If the value is not scalar (list or nested structure) at the specified path, returns NULL.

See examples

SELECT
    json_value(js_data, 'first_name')      AS first_name,
    json_value(js_data, '$.phone_numbers[*].number') AS phone_numbers,
    json_value(js_data, '$.children[0]')   AS child_1,
    json_value(js_data, '$.phone_numbers') AS phone_numbers, -- NULL expected
    json_value(js_data, '$.address')       AS address,       -- NULL expected
FROM js_table;

+------------+---------------------------------+-----------+---------------+---------+
| first_name | phone_numbers                   | child_1   | phone_numbers | address |
+------------+---------------------------------+-----------+---------------+---------+
| John       | {"212 555-1234","646 555-4567"} | Catherine | null          | null    |
+------------+---------------------------------+-----------+---------------+---------+

`json()`

Description	Shortens the JSON structure record (removes spaces and line breaks).
Usage	`json(json)`.

See examples

SELECT
    json('{
            "first_name": "John",
            "last_name": "Smith"
          }
        ') AS result_1,
    json(js_data) AS result_2,
FROM js_table;

+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| result_1                                            | result_2                                                                                                                                                                                                                                                                                                                                                                                           |
+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| "{""first_name"":""John"",""last_name"":""Smith""}" | "{""first_name"":""John"",""last_name"":""Smith"",""is_alive"":true,""age"":27,""address"":{""street_address"":""21 2nd Street"",""city"":""New York"",""state"":""NY"",""postal_code"":""10021-3100""},""phone_numbers"":[{""type"":""home"",""number"":""212 555-1234""},{""type"":""office"",""number"":""646 555-4567""}],""children"":[""Catherine"",""Thomas"",""Trevor""],""spouse"":null}" |
+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

`read_json()`

Description	reads a `.json` file and writes the read data to a table.
Usage	`read_json(filename[, columns = {column_name: 'column_type', ...}])`
Aliases	`read_json_auto()`

The data types for the columns are automatically determined.

Parameters

columns — optional parameter, where you can specify column names and types. In this case, only the specified columns will be added to the resulting table.

More details on loading data into Tengri are described on the page Data loading.

The examples use file tengri_data_types.json with data types Tengri and their brief descriptions and file json_example_from_wikipedia.json with this example.

See examples

Let’s read the file tengri_data_types.json and output the first five rows of the table:

SELECT *
FROM read_json(
    '<path>/tengri_data_types.json'
)
LIMIT 5

+----------+-----------+----------+---------------------------+
| name     | type      | category | description               |
+----------+-----------+----------+---------------------------+
| BIGINT   | data type | numeric  | Целые числа.               |
+----------+-----------+----------+---------------------------+
| BIGINT[] | data type | array    | Массивы целых чисел.       |
+----------+-----------+----------+---------------------------+
| BLOB     | data type | blob     | Двоичные объекты.          |
+----------+-----------+----------+---------------------------+
| BOOL     | data type | boolean  | Булевы значения.           |
+----------+-----------+----------+---------------------------+
| BOOL[]   | data type | array    | Массивы булевых значений.   |
+----------+-----------+----------+---------------------------+

Let’s read the file tengri_data_types.json, set only the required columns (the keys of the source file) and output the first five rows of the table:

SELECT *
FROM read_json(
    '<path>/tengri_data_types.json',
    columns = {name: 'VARCHAR', category: 'VARCHAR'})
LIMIT 5

+----------+----------+
| name     | category |
+----------+----------+
| BIGINT   | numeric  |
+----------+----------+
| BIGINT[] | array    |
+----------+----------+
| BLOB     | blob     |
+----------+----------+
| BOOL     | boolean  |
+----------+----------+
| BOOL[]   | array    |
+----------+----------+

Let’s read the file json_example_from_wikipedia.json and output the whole table built on it. Note that nested JSON structures are written to the table cells and are not expanded in any way (columns address, phone_numbers, children). To expand nested structures, you can use the function unnest.

SELECT *
FROM read_json(
    '<path>/json_example_from_wikipedia.json'
)

+------------+-----------+----------+-----+-----------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+---------------------------+--------+
| first_name | last_name | is_alive | age | address                                                                                             | phone_numbers                                                                             | children                  | spouse |
+------------+-----------+----------+-----+-----------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+---------------------------+--------+
| John       | Smith     | true     | 27  | {"street_address": "21 2nd Street", "city": "New York", "state": "NY", "postal_code": "10021-3100"} | {{'type': 'home', 'number': '212 555-1234'},{'type': 'office', 'number': '646 555-4567'}} | {Catherine,Thomas,Trevor} | null   |
+------------+-----------+----------+-----+-----------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+---------------------------+--------+