Функции для JSON

Функции для работы с файлами расширения .json и с типом данных JSON.

Путь внутри структуры JSON

Во многих функциях для JSON используется путь внутри структуры JSON как один из аргументов. Путь может задаваться в любой из двух нотаций по следующим стандартам:

  • JSONPath

    • $.key1.key2 — обращение к значению ключа key2

    • $.key1.key2[i] — обращение к -му элементу списка в значении ключа key2

  • JSON Pointer

    • /key1/key2 — обращение к значению ключа key2

    • /key1/key2/i — обращение к -му элементу списка в значении ключа key2

Нумерация элементов списка в структуре JSON начинается с 0.

В примерах ниже используется структура JSON из этого примера:

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

Описание

Возвращает количество элементов в массиве по указанному пути в JSON или 0, если по указанному пути не массив.
Использование

json_array_length(json[, path])

Если во втором аргументе задан список путей, то результатом будет список длин массивов по указанным путям.

Посмотреть примеры 
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()

Описание

Проверяет, есть ли в структуре JSON подструктура JSON, заданная во втором аргументе.
Использование

json_contains(json, json)

Оба аргумента должны иметь тип JSON. Второй аргумент может быть числовым значением или текстовой строкой, при этом текстовая строка должна быть заключена в двойные кавычки.

Посмотреть примеры 
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()

Описание

Проверяет, есть ли в структуре JSON указанный путь.
Использование

json_exists(json, path)

Возвращает BOOL или массив BOOL[] для случаев, когда путь указывает на элементы списка в структуре JSON.

Посмотреть примеры 
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()

Описание

Извлекает данные из структуры JSON по указанному пути. Возвращает данные в виде JSON.
Использование

json_extract(json, path) или json_extract(json, [path1, path2, ...])
Псевдонимы

json_extract_path

Если в качестве второго аргумента задан список путей, то возвращает список значений.

Посмотреть примеры 
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()

Описание

Извлекает данные из структуры JSON по указанному пути. Возвращает данные в виде VARCHAR.
Использование

json_extract_string(json, path) или json_extract_string(json, [path1, path2, ...])
Псевдонимы

json_extract_path_text

Если в качестве второго аргумента задан список путей, то возвращает список значений.

Посмотреть примеры 
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()

Описание

Возвращает список JSON, содержащий все значения столбца.
Использование

json_group_array(argument)
Функция изменяет кардинальность данных.
Посмотреть примеры 
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()

Описание

Возвращает структуру JSON, содержащую все пары key-value из столбцов, указанных в аргументах.
Использование

json_group_object(argument1, argument2)
Функция изменяет кардинальность данных.
Посмотреть примеры 
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()

Описание

Возвращает все ключи из указанной структуры JSON в виде VARCHAR[].
Использование

json_keys(json[, path])

Если во втором аргументе указан путь, то возвращает ключи структуры JSON по указанному пути. Если задан список путей, то результатом будет список списков ключей.

Посмотреть примеры 
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()

Описание

Трансформирует структуру JSON в соответствии с указанной структурой.
Использование

json_transform(json, string)
Псевдонимы

from_json()

Целевая структура указывается в виде текстовой строки во втором аргументе.

В случаях пропусков значений в исходной структуре JSON в результат проставляются значения NULL.

В случаях невозможности преобразовать типы данных в исходной структуре к указанным проставляется значение NULL.

Посмотреть примеры 
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()

Описание

Трансформирует структуру JSON в соответствии с указанной структурой. Выдает ошибку в случае несоответствия структур или типов.
Использование

json_transform_strict(json, string)
Псевдонимы

from_json_strict()

Целевая структура указывается в виде текстовой строки во втором аргументе.

В случаях, если в исходной структуре JSON отсутствуют значения из указанной структуры, выдает ошибку.

В случаях невозможности преобразовать типы данных в исходной структуре к указанным выдает ошибку.

Посмотреть примеры 
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()

Описание

Проверяет, является ли аргумент валидной структурой JSON.
Использование

json_valid(json)
Посмотреть примеры 
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()

Описание

Извлекает значения из структуры JSON по указанному пути.
Использование

json_value(json, path)

Если по указанному пути значение не скалярное (список или вложенная структура), то возвращает NULL.

Посмотреть примеры 
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()

Описание

Сокращает запись структуры JSON (убирает пробелы и переносы строк).
Использование

json(json)
Посмотреть примеры 
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()

Описание

Читает файл .json и записывает прочитанные данные в таблицу.
Использование

read_json(filename[, columns = {column_name: 'column_type', ...}])
Псевдонимы

read_json_auto()

Типы данных для столбцов определяются автоматически.

Параметры

  • columns — опциональный параметр, в котором можно указать имена столбцов и их типы. В таком случае в результирующую таблицу будут добавлены только указанные столбцы.

Подробнее о загрузке данных в Tengri можно узнать на странице Загрузка данных.

В примерах используется файл tengri_data_types.json с типами данных Tengri и их краткими описаниями и файл json_example_from_wikipedia.json с этим примером.
Посмотреть примеры

Прочитаем файл tengri_data_types.json и выведем первые пять строк таблицы:

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    | Массивы булевых значений.   |
+----------+-----------+----------+---------------------------+

Прочитаем файл tengri_data_types.json, зададим только нужные колонки (ключи исходного файла) и выведем первые пять строк таблицы:

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

Прочитаем файл json_example_from_wikipedia.json и выведем всю построенную по нему таблицу. Обратим внимание на то, что вложенные структуры JSON записываются в ячейки таблицы и никак не разворачиваются (столбцы address, phone_numbers, children). Для разворачивания вложенных структур можно использовать функцию 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   |
+------------+-----------+----------+-----+-----------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+---------------------------+--------+