JSON

Начиная с версии 1.5.0, Selena поддерживает JSON. Эта статья знакомит с основными концепциями JSON и тем, как Selena создает столбцы типа JSON, загружает и запрашивает JSON-данные, а также конструирует и обрабатывает JSON-данные с помощью JSON-функций и операторов.

Что такое JSON

JSON — это легковесный формат обмена данными. JSON-данные являются полуструктурированными и поддерживают древовидную структуру. JSON-данные иерархичны, гибки, легко читаются и обрабатываются, и широко используются в сценариях хранения и анализа данных. JSON поддерживает такие типы данных, как NUMBER, STRING, BOOLEAN, ARRAY, OBJECT и значения NULL.

Для получения дополнительной информации о JSON обратитесь к официальному сайту JSON. Для синтаксиса ввода и вывода JSON-данных обратитесь к спецификации JSON RFC 7159.

Selena поддерживает хранение и эффективные запросы и анализ JSON-данных. Selena использует кодирование в двоичном формате для хранения JSON-данных вместо прямого хранения входного текста, что снижает затраты на парсинг во время вычислений и запросов данных, тем самым повышая эффективность запросов.

Использование JSON-данных

Создание столбцов типа JSON

При создании таблицы укажите столбец j как тип JSON, используя ключевое слово JSON.

CREATE TABLE `tj` (
    `id` INT(11) NOT NULL COMMENT "",
    `j`  JSON NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`id`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`id`)
PROPERTIES (
    "replication_num" = "3",
    "storage_format" = "DEFAULT"
);

Загрузка данных и сохранение как тип JSON

Selena поддерживает следующие методы загрузки данных и сохранения их как тип JSON.

Метод 1: Используйте INSERT INTO для записи данных в столбец типа JSON (например, столбец j).

INSERT INTO tj (id, j) VALUES (1, parse_json('{"a": 1, "b": true}'));
INSERT INTO tj (id, j) VALUES (2, parse_json('{"a": 2, "b": false}'));
INSERT INTO tj (id, j) VALUES (3, parse_json('{"a": 3, "b": true}'));
INSERT INTO tj (id, j) VALUES (4, json_object('a', 4, 'b', false)); 

Функция PARSE_JSON может конструировать JSON-данные на основе строковых данных. Функция JSON_OBJECT может конструировать JSON-данные объектного типа, позволяя преобразовывать существующие таблицы в тип JSON. Для получения дополнительной информации обратитесь к PARSE_JSON и JSON_OBJECT.

Метод 2: Используйте Stream Load для импорта JSON-файлов и сохранения их как тип JSON. Для методов импорта обратитесь к Импорт JSON-данных.
- Чтобы импортировать и сохранить JSON-объект в корневом узле JSON-файла как тип JSON, установите jsonpaths в $.
- Чтобы импортировать и сохранить значение JSON-объекта в JSON-файле как тип JSON, установите jsonpaths в $.a (где a представляет ключ). Для получения дополнительных выражений JSON path обратитесь к JSON path.
Метод 3: Используйте Broker Load для импорта Parquet-файлов и сохранения их как тип JSON. Для методов импорта обратитесь к Broker Load.

Преобразование типов данных поддерживается во время импорта следующим образом:

Тип данных в Parquet-файле	Преобразованный тип JSON-данных
Целочисленные типы (INT8, INT16, INT32, INT64, UINT8, UINT16, UINT32, UINT64)	JSON Number
Типы с плавающей точкой (FLOAT, DOUBLE)	JSON Number
BOOLEAN	JSON Boolean
STRING	JSON String
MAP	JSON Object
STRUCT	JSON Object
LIST	JSON Array
UNION, TIMESTAMP и другие типы	Не поддерживается

Метод 4: Используйте Routine Load для непрерывного потребления JSON-данных из Kafka и импорта их в Selena.

Запросы и обработка JSON-данных

Selena поддерживает запросы и обработку JSON-данных и поддерживает использование JSON-функций и операторов.

В этом примере используется таблица tj для иллюстрации.

mysql> select * from tj;
+------+----------------------+
| id   |          j           |
+------+----------------------+
| 1    | {"a": 1, "b": true}  |
| 2    | {"a": 2, "b": false} |
| 3    | {"a": 3, "b": true}  |
| 4    | {"a": 4, "b": false} |
+------+----------------------+

Пример 1: Фильтрация данных в столбце типа JSON, которые соответствуют условию id=1.

mysql> select * from tj where id = 1;
+------+---------------------+
| id   |           j         |
+------+---------------------+
| 1    | {"a": 1, "b": true} |
+------+---------------------+

Пример 2: Фильтрация данных в таблице на основе столбца типа JSON.

В следующем примере j->'a' возвращает JSON-данные. Вы можете сравнить это с первым примером, который выполняет неявное преобразование данных; или использовать функцию CAST для конструирования JSON-данных как INT для сравнения.

mysql> select * from tj where j->'a' = 1;
+------+---------------------+
| id   | j                   |
+------+---------------------+
|    1 | {"a": 1, "b": true} |
+------+---------------------+

mysql> select * from tj where cast(j->'a' as INT) = 1; 
+------+---------------------+
|   id |         j           |
+------+---------------------+
|   1  | {"a": 1, "b": true} |
+------+---------------------+

Пример 3: Фильтрация данных в таблице на основе столбца типа JSON (вы можете использовать функцию CAST для конструирования столбца типа JSON как тип BOOLEAN).

mysql> select * from tj where cast(j->'b' as boolean);
+------+---------------------+
|  id  |          j          |
+------+---------------------+
| 1    | {"a": 1, "b": true} |
| 3    | {"a": 3, "b": true} |
+------+---------------------+

Пример 4: Фильтрация данных в столбце типа JSON, которые соответствуют условию, и выполнение числовых операций.

mysql> select cast(j->'a' as int) from tj where cast(j->'b' as boolean);
+-----------------------+
|  CAST(j->'a' AS INT)  |
+-----------------------+
|          3            |
|          1            |
+-----------------------+

mysql> select sum(cast(j->'a' as int)) from tj where cast(j->'b' as boolean);
+----------------------------+
| sum(CAST(j->'a' AS INT))  |
+----------------------------+
|              4             |
+----------------------------+

Пример 5: Сортировка на основе столбца типа JSON.

mysql> select * from tj
       where j->'a' <= 3
       order by cast(j->'a' as int);
+------+----------------------+
| id   | j                    |
+------+----------------------+
|    1 | {"a": 1, "b": true}  |
|    2 | {"a": 2, "b": false} |
|    3 | {"a": 3, "b": true}  |
|    4 | {"a": 4, "b": false} |
+------+----------------------+

JSON-функции и операторы

JSON-функции и операторы могут использоваться для конструирования и обработки JSON-данных. Для получения подробной информации обратитесь к JSON-функции и операторы.

JSON Array

JSON может содержать вложенные данные, такие как Objects, Arrays или другие типы JSON-данных, вложенные в Array. Selena предоставляет богатый набор функций и операторов для обработки этих сложных вложенных JSON-структур данных. Далее будет представлено, как обрабатывать JSON-данные, содержащие массивы.

Предположим, что в таблице events есть JSON-поле event_data со следующим содержимым:

{
  "user": "Alice",
  "actions": [
    {"type": "click", "timestamp": "2024-03-17T10:00:00Z", "quantity": 1},
    {"type": "view", "timestamp": "2024-03-17T10:05:00Z", "quantity": 2},
    {"type": "purchase", "timestamp": "2024-03-17T10:10:00Z", "quantity": 3}
  ]
}

Следующие примеры демонстрируют несколько распространенных сценариев анализа JSON-массивов:

Извлечение элементов массива: Извлечение конкретных полей, таких как type, timestamp и т.д., из массива actions и выполнение операций проекции.
Развертывание массива: Использование функции json_each для развертывания вложенного JSON-массива в многострочную, многостолбцовую табличную структуру для последующего анализа.
Вычисления с массивами: Использование Array Functions для фильтрации, преобразования и агрегации элементов массива, например, подсчет количества операций определенного типа.

1. Извлечение элементов из JSON Array

Для извлечения вложенного элемента из JSON Array можно использовать следующий синтаксис:

Возвращаемый тип по-прежнему является JSON Array, и вы можете использовать выражение CAST для преобразования типа.

MySQL > SELECT json_query(event_data, '$.actions[*].type') as json_array FROM events;
+-------------------------------+
| json_array                    |
+-------------------------------+
| ["click", "view", "purchase"] |
+-------------------------------+

MySQL > SELECT cast(json_query(event_data, '$.actions[*].type') as array<string>) array_string FROM events;
+-----------------------------+
| array_string                |
+-----------------------------+
| ["click","view","purchase"] |
+-----------------------------+

2. Развертывание с использованием json_each

Selena предоставляет функцию json_each для развертывания JSON-массивов, преобразуя их в несколько строк данных. Например:

MySQL > select value from events, json_each(event_data->'actions');
+--------------------------------------------------------------------------+
| value                                                                    |
+--------------------------------------------------------------------------+
| {"quantity": 1, "timestamp": "2024-03-17T10:00:00Z", "type": "click"}    |
| {"quantity": 2, "timestamp": "2024-03-17T10:05:00Z", "type": "view"}     |
| {"quantity": 3, "timestamp": "2024-03-17T10:10:00Z", "type": "purchase"} |
+--------------------------------------------------------------------------+

Для отдельного извлечения полей type и timestamp:

MySQL > select value->'timestamp', value->'type' from events, json_each(event_data->'actions');
+------------------------+---------------+
| value->'timestamp'     | value->'type' |
+------------------------+---------------+
| "2024-03-17T10:00:00Z" | "click"       |
| "2024-03-17T10:05:00Z" | "view"        |
| "2024-03-17T10:10:00Z" | "purchase"    |
+------------------------+---------------+

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

3. Использование Array Functions для фильтрации и вычислений

Selena также поддерживает функции, связанные с ARRAY, которые можно использовать в сочетании с JSON-функциями для более эффективных запросов. Комбинируя эти функции, вы можете фильтровать, преобразовывать и агрегировать JSON-данные массивов. Следующий пример демонстрирует, как использовать эти функции:

MySQL > 
WITH step1 AS (
 SELECT cast(event_data->'actions' as ARRAY<JSON>) as docs
   FROM events
)
SELECT array_filter(doc -> get_json_string(doc, 'type') = 'click', docs) as clicks
FROM step1
+---------------------------------------------------------------------------+
| clicks                                                                    |
+---------------------------------------------------------------------------+
| ['{"quantity": 1, "timestamp": "2024-03-17T10:00:00Z", "type": "click"}'] |
+---------------------------------------------------------------------------+

Кроме того, вы можете комбинировать другие Array Functions для выполнения агрегационных вычислений на элементах массива:

MySQL > 
WITH step1 AS (
 SELECT cast(event_data->'actions' as ARRAY<JSON>) as docs
   FROM events
), step2 AS (
    SELECT array_filter(doc -> get_json_string(doc, 'type') = 'click', docs) as clicks
    FROM step1
)
SELECT array_sum(
            array_map(doc -> get_json_double(doc, 'quantity'), clicks)
            ) as click_amount
FROM step2
+--------------+
| click_amount |
+--------------+
| 1.0          |
+--------------+

Ограничения и соображения

Максимальная поддерживаемая длина для JSON-данных в настоящее время составляет 16 МБ.
Предложения ORDER BY, GROUP BY и JOIN не поддерживают ссылки на столбцы типа JSON. Если вам нужно на них ссылаться, вы можете заранее использовать функцию CAST для преобразования столбцов типа JSON в другие SQL-типы. Для конкретных методов преобразования обратитесь к Преобразование типа JSON.
Столбцы типа JSON могут существовать в таблицах Duplicate Key, Primary Key и Unique Key, но не в таблицах Aggregate.
Столбцы типа JSON не поддерживаются в качестве ключей разделов, ключей группировки или столбцов измерений в таблицах Duplicate Key, Primary Key и Unique Key, и не могут использоваться в предложениях JOIN, GROUP BY или ORDER BY.
Selena поддерживает использование операторов <, <=, >, >=, =, != для запроса JSON-данных, но не поддерживает оператор IN.

Что такое JSON​

Использование JSON-данных​

Создание столбцов типа JSON​

Загрузка данных и сохранение как тип JSON​

Запросы и обработка JSON-данных​

JSON-функции и операторы​

JSON Array​

1. Извлечение элементов из JSON Array​

2. Развертывание с использованием json_each​

3. Использование Array Functions для фильтрации и вычислений​

Ограничения и соображения​