Functions for 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()`

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

`json_array()`

Description	Creates a JSON list from passed arguments.
Usage	`json_array(argument, ...)`

See examples

SELECT json_array(1, 2, 3, 'Tengri');

+------------------+
| json_array       |
+------------------+
| [1,2,3,"Tengri"] |
+------------------+

SELECT json_valid(json_array(1, 2, 3, 'Tengri')) AS result;

+--------+
| result |
+--------+
| true   |
+--------+

`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_each()`

Description	traverses the JSON structure and returns all parameters of the top-level structure element.
Usage	`json_each(json[ ,path])`

If the element is not an array or object, the element itself is returned. The optional path argument specifies the path to the element to start the traversal from (by default, the traversal starts at the root element).

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
    je.*
FROM example AS e
INNER JOIN json_each(e.js_data) AS je;

+-----------------------------------------------------------+------------+---------+---------+---------+----+--------+--------------+------+-------+
| js_data                                                   | key        | value   | type    | atom    | id | parent | fullkey      | path | rowid |
+-----------------------------------------------------------+------------+---------+---------+---------+----+--------+--------------+------+-------+
| { "first_name": "John", "is_alive": true, "age": 28 }     | first_name | "John"  | VARCHAR | "John"  | 2  | null   | $.first_name | $    | 0     |
+-----------------------------------------------------------+------------+---------+---------+---------+----+--------+--------------+------+-------+
| { "first_name": "John", "is_alive": true, "age": 28 }     | is_alive   | true    | BOOLEAN | true    | 4  | null   | $.is_alive   | $    | 1     |
+-----------------------------------------------------------+------------+---------+---------+---------+----+--------+--------------+------+-------+
| { "first_name": "John", "is_alive": true, "age": 28 }     | age        | 28      | UBIGINT | 28      | 6  | null   | $.age        | $    | 2     |
+-----------------------------------------------------------+------------+---------+---------+---------+----+--------+--------------+------+-------+
| { "first_name": "John", "last_name": "Smith", "age": 27 } | first_name | "John"  | VARCHAR | "John"  | 2  | null   | $.first_name | $    | 0     |
+-----------------------------------------------------------+------------+---------+---------+---------+----+--------+--------------+------+-------+
| { "first_name": "John", "last_name": "Smith", "age": 27 } | last_name  | "Smith" | VARCHAR | "Smith" | 4  | null   | $.last_name  | $    | 1     |
+-----------------------------------------------------------+------------+---------+---------+---------+----+--------+--------------+------+-------+
| { "first_name": "John", "last_name": "Smith", "age": 27 } | age        | 27      | UBIGINT | 27      | 6  | null   | $.age        | $    | 2     |
+-----------------------------------------------------------+------------+---------+---------+---------+----+--------+--------------+------+-------+

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 je.*
FROM example AS e
INNER JOIN json_each(e.js_data, '$.age') AS je;

+-----------------------------------------------------------+------+-------+---------+------+----+--------+---------+-------+
| js_data                                                   | key  | value | type    | atom | id | parent | fullkey | path  |
+-----------------------------------------------------------+------+-------+---------+------+----+--------+---------+-------+
| { "first_name": "John", "is_alive": true, "age": 28 }     | null | 28    | UBIGINT | 28   | 6  | null   | $.age   | $.age |
+-----------------------------------------------------------+------+-------+---------+------+----+--------+---------+-------+
| { "first_name": "John", "last_name": "Smith", "age": 27 } | null | 27    | UBIGINT | 27   | 6  | null   | $.age   | $.age |
+-----------------------------------------------------------+------+-------+---------+------+----+--------+---------+-------+

`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.

For faster access to JSON structure fields, use the additional type MAP(VARCHAR, VARCHAR).

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(column)`

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(column1, column2)`

The function changes 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_merge_patch()`

Description	Merges multiple JSON structures into one.
Usage	`json_merge_patch(argument1, argument2, ...)`.

See examples

SELECT json_merge_patch('{"Tengri": 6}', '{"TNGRi": 5}');

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

`json_object()`

Description	Creates a JSON structure from key and value pairs passed as a list of arguments.
Usage	`json_object(key1, value1, key2, value2, ...)`

Requires an even number of arguments.

See examples

SELECT json_object('Tengri', 6, 'TNGRi', 5);

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

`json_structure()`

Description	Returns a description of the JSON structure.
Usage	`json_structure(column)`

Returns a description of the JSON structure — field names and their data types.

The data type names in the results of this function may not match the names of data types Tengri.

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_structure(js_data)
FROM example;

+------------------------------------------------------------------------------+
| json_structure                                                               |
+------------------------------------------------------------------------------+
| "{""first_name"":""VARCHAR"",""last_name"":""VARCHAR"",""age"":""UBIGINT""}" |
+------------------------------------------------------------------------------+
| "{""first_name"":""VARCHAR"",""is_alive"":""BOOLEAN"",""age"":""UBIGINT""}"  |
+------------------------------------------------------------------------------+

SELECT
    json_structure(js_data)
FROM js_table;

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| json_structure                                                                                                                                                                                                                                                                                                                             |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| "{""first_name"":""VARCHAR"",""last_name"":""VARCHAR"",""is_alive"":""BOOLEAN"",""age"":""UBIGINT"",""address"":{""street_address"":""VARCHAR"",""city"":""VARCHAR"",""state"":""VARCHAR"",""postal_code"":""VARCHAR""},""phone_numbers"":[{""type"":""VARCHAR"",""number"":""VARCHAR""}],""children"":[""VARCHAR""],""spouse"":""NULL""}" |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

`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_tree()`

Description	traverses a JSON structure and returns all parameters of each element of the structure.
Usage	`json_tree(json[ ,path])`

Traverses the JSON structure (in depth priority order) and returns a single string for each element of the structure with all element parameters.

If the element is not an array or object, the element itself is returned. The optional path argument specifies the path to the element to start traversal from (by default, traversal starts at the root element).

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
    je.*
FROM example AS e
INNER JOIN json_tree(e.js_data) AS je;

+-----------------------------------------------------------+------------+----------------------------------------------------------------+---------+---------+----+--------+--------------+------+
| js_data                                                   | key        | value                                                          | type    | atom    | id | parent | fullkey      | path |
+-----------------------------------------------------------+------------+----------------------------------------------------------------+---------+---------+----+--------+--------------+------+
| { "first_name": "John", "is_alive": true, "age": 28 }     | null       | "{""first_name"":""John"",""is_alive"":true,""age"":28}"       | OBJECT  | null    | 0  | null   | $            | $    |
+-----------------------------------------------------------+------------+----------------------------------------------------------------+---------+---------+----+--------+--------------+------+
| { "first_name": "John", "is_alive": true, "age": 28 }     | first_name | "John"                                                         | VARCHAR | "John"  | 2  | 0      | $.first_name | $    |
+-----------------------------------------------------------+------------+----------------------------------------------------------------+---------+---------+----+--------+--------------+------+
| { "first_name": "John", "is_alive": true, "age": 28 }     | is_alive   | true                                                           | BOOLEAN | true    | 4  | 0      | $.is_alive   | $    |
+-----------------------------------------------------------+------------+----------------------------------------------------------------+---------+---------+----+--------+--------------+------+
| { "first_name": "John", "is_alive": true, "age": 28 }     | age        | 28                                                             | UBIGINT | 28      | 6  | 0      | $.age        | $    |
+-----------------------------------------------------------+------------+----------------------------------------------------------------+---------+---------+----+--------+--------------+------+
| { "first_name": "John", "last_name": "Smith", "age": 27 } | null       | "{""first_name"":""John"",""last_name"":""Smith"",""age"":27}" | OBJECT  | null    | 0  | null   | $            | $    |
+-----------------------------------------------------------+------------+----------------------------------------------------------------+---------+---------+----+--------+--------------+------+
| { "first_name": "John", "last_name": "Smith", "age": 27 } | first_name | "John"                                                         | VARCHAR | "John"  | 2  | 0      | $.first_name | $    |
+-----------------------------------------------------------+------------+----------------------------------------------------------------+---------+---------+----+--------+--------------+------+
| { "first_name": "John", "last_name": "Smith", "age": 27 } | last_name  | "Smith"                                                        | VARCHAR | "Smith" | 4  | 0      | $.last_name  | $    |
+-----------------------------------------------------------+------------+----------------------------------------------------------------+---------+---------+----+--------+--------------+------+
| { "first_name": "John", "last_name": "Smith", "age": 27 } | age        | 27                                                             | UBIGINT | 27      | 6  | 0      | $.age        | $    |
+-----------------------------------------------------------+------------+----------------------------------------------------------------+---------+---------+----+--------+--------------+------+

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
    je.*
FROM example AS e
INNER JOIN json_tree(e.js_data, '$.age') AS je;

+-----------------------------------------------------------+------+-------+---------+------+----+--------+---------+-------+
| js_data                                                   | key  | value | type    | atom | id | parent | fullkey | path  |
+-----------------------------------------------------------+------+-------+---------+------+----+--------+---------+-------+
| { "first_name": "John", "is_alive": true, "age": 28 }     | null | 28    | UBIGINT | 28   | 6  | null   | $.age   | $.age |
+-----------------------------------------------------------+------+-------+---------+------+----+--------+---------+-------+
| { "first_name": "John", "last_name": "Smith", "age": 27 } | null | 27    | UBIGINT | 27   | 6  | null   | $.age   | $.age |
+-----------------------------------------------------------+------+-------+---------+------+----+--------+---------+-------+

`json_type()`

Description	Returns the data type of a field in a JSON structure.
Usage	`json_type(json[, path])`

The data type names in the results of this function may not match the names of data types Tengri.

See examples

SELECT
    json_type(js_data)                    AS result_1,
    json_type(js_data, '$.first_name')    AS result_2,
    json_type(js_data, '$.age')           AS result_3,
    json_type(js_data, '$.phone_numbers') AS result_4,
FROM js_table;

+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 |
+----------+----------+----------+----------+
| OBJECT   | VARCHAR  | UBIGINT  | ARRAY    |
+----------+----------+----------+----------+

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

`read_json()`

Description	Reads a `.json` file and returns the read data as a table.
Usage	`read_json(filename [, <parameters>])`
Aliases	`read_json_auto()`

The data types for the columns are automatically determined.

read_json_auto — a function alias with the format parameter set to auto.

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.
filename — an optional parameter that allows you to add the source file name to a separate column in the resul table.
The specified parameter value will be used as the column name. If the parameter is not specified, the column will not be added.

For example:
- filename = 'source_file' — a column named source_file will be added.
format — optional parameter that specifies the format for reading the JSON structure.
Possible values:
- format = 'auto' — the format of reading is defined automatically.
- format = 'unstructured' — the top-level data of the JSON structure is read.
- format = 'newline_delimited' — reads data from NDJSON, where each JSON object is separated by a newline character \n.
- format = 'array' — all list elements of the top-level list of the JSON structure are read.
ignore_errors — optional parameter to ignore read errors.
Possible values:
- ignore_errors = 'true' — ignore errors.
- ignore_errors = 'false' — do not ignore errors (default).

For more details on uploading data to Tengri, see Data upload.

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, output the first five rows of the table and add a column with source file name:

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

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

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

`read_json_objects()`

Description	Reads a `.json` file and returns a list.
Usage	`read_json_objects(filename [, <parameters>])`

Reads a .json file and returns a list of the top-level objects of the JSON structure.

Parameters

filename — an optional parameter that allows you to add the source file name to a separate column in the resul table.
The specified parameter value will be used as the column name. If the parameter is not specified, the column will not be added.

For example:
- filename = 'source_file' — a column named source_file will be added.
format — optional parameter that specifies the format for reading the JSON structure.
Possible values:
- format = 'auto' — the format of reading is defined automatically.
- format = 'unstructured' — the top-level data of the JSON structure is read.
- format = 'newline_delimited' — reads data from NDJSON, where each JSON object is separated by a newline character \n.
- format = 'array' — all list elements of the top-level list of the JSON structure are read.

The examples use the file tengri_data_types.json with the data types Tengri and their brief descriptions.

See examples

Let’s read the file .tengri_data_types.json in array read mode and output the first five lines:

SELECT *
FROM read_json_objects('<path>/tengri_data_types.json', format = 'array')
LIMIT 5

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

Let’s read the file tengri_data_types.json in unstructured reading mode and get one element (with full structure from the file) as a result:

SELECT *
FROM read_json_objects('<path>/tengri_data_types.json', format = 'unstructured')

+----------------------------------------------+
| json                                         |
+----------------------------------------------+
|  [                                           |
|    {                                         |
|        "name": "BIGINT",                     |
|        "type": "data type",                  |
|        "category": "numeric",                |
|        "description": "Целые числа."         |
|    },                                        |
|    {                                         |
|        "name": "BIGINT[]",                   |
|        "type": "data type",                  |
|        "category": "array",                  |
|        "description": "Массивы целых чисел." |
|    },                                        |
| ...                                          |
+----------------------------------------------+

`to_json()`

Description	Creates a JSON structure from an argument of any type.
Usage	`to_json(argument)`
Aliases	`json_quote()`, `array_to_json()`

array_to_json — accepts only lists as arguments.

See examples

SELECT to_json('Tengri');

+----------+
| to_json  |
+----------+
| "Tengri" |
+----------+

SELECT json_valid(to_json('Tengri')) AS result;

+--------+
| result |
+--------+
| true   |
+--------+

SELECT array_to_json([1, 2, 3]);

+---------------+
| array_to_json |
+---------------+
| [1,2,3]       |
+---------------+

SELECT json_valid(array_to_json([1, 2, 3])) AS result;

+--------+
| result |
+--------+
| true   |
+--------+

Functions for JSON

Path inside JSON structure

json()

json_array()

json_array_length()

json_contains()

json_each()

json_exists()

json_extract()

json_extract_string()

json_group_array()

json_group_object()

json_keys()

json_merge_patch()

json_object()

json_structure()

json_transform()

json_transform_strict()

json_tree()

json_type()

json_valid()

json_value()

read_json()

Parameters

read_json_objects()

Parameters

to_json()

See also

`json()`

`json_array()`

`json_array_length()`

`json_contains()`

`json_each()`

`json_exists()`

`json_extract()`

`json_extract_string()`

`json_group_array()`

`json_group_object()`

`json_keys()`

`json_merge_patch()`

`json_object()`

`json_structure()`

`json_transform()`

`json_transform_strict()`

`json_tree()`

`json_type()`

`json_valid()`

`json_value()`

`read_json()`

`read_json_objects()`

`to_json()`