Каталог со вложенными атрибутами

В этом рецепте показано, как использовать JSON-индекс для ускорения доступа к каталогу товаров, в котором характеристики товара хранятся в виде JSON-документа с произвольным набором полей. Такая схема удобна, когда:

• набор атрибутов товара заранее неизвестен или различается для разных категорий;
• добавлять новые атрибуты через ALTER TABLE ADD COLUMN нежелательно;
• нужно эффективно фильтровать товары по различным сочетаниям атрибутов.

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

Создайте таблицу и индекс

CREATE TABLE products (
    sku_id Uint64,
    attrs JsonDocument,
    PRIMARY KEY (sku_id),
    INDEX attrs_json_idx GLOBAL USING json ON (attrs)
);

В этой схеме:

• sku_id — числовой идентификатор товара.
• attrs — атрибуты товара в формате JsonDocument. Хранение в виде JsonDocument экономит место и ускоряет десериализацию по сравнению с Json.
• attrs_json_idx — JSON-индекс по колонке attrs. Обновляется синхронно вместе с основной таблицей.

Загрузите тестовые данные

UPSERT INTO products (sku_id, attrs) VALUES
    (10, JsonDocument(@@{
        "brand": "ACME",
        "price": 49.90,
        "category": "tools",
        "warehouses": [{"id": 1, "stock": 12}, {"id": 2, "stock": 0}]
    }@@)),
    (11, JsonDocument(@@{
        "brand": "ACME",
        "price": 199.00,
        "category": "electronics",
        "warehouses": [{"id": 1, "stock": 3}]
    }@@)),
    (12, JsonDocument(@@{
        "brand": "Globex",
        "price": 25.00,
        "category": "tools",
        "warehouses": [{"id": 2, "stock": 0}]
    }@@));

Фильтр по бренду и диапазону цены

SELECT sku_id, attrs
FROM products VIEW attrs_json_idx
WHERE JSON_VALUE(attrs, '$.brand' RETURNING Utf8) = "ACME"u
  AND JSON_VALUE(attrs, '$.price' RETURNING Double) BETWEEN 10.0 AND 100.0;

Что происходит:

• Для условия $.brand = "ACME" в индекс попадает токен «путь + значение» — это даёт точное совпадение и максимальную селективность.
• Для условия BETWEEN 10.0 AND 100.0 в индекс попадает только токен пути $.price. Это сужает множество строк до тех, у которых поле price присутствует, после чего движок исполнения запросов проверяет диапазон точным сравнением (пост-фильтр).
• Условия объединены через AND — это позволяет использовать индекс для проверки сразу обоих фрагментов, в результате чтение по индексу будет минимальным.

Результат:

sku_id attrs
10     {"brand":"ACME","price":49.9,...}

Поиск по вложенному массиву

JsonPath поддерживает обращение к элементам массивов и фильтры внутри пути. Например, можно найти товары, у которых хотя бы на одном складе есть остаток:

SELECT sku_id
FROM products VIEW attrs_json_idx
WHERE JSON_EXISTS(attrs, '$.warehouses ? (@.stock > 0)');

Для поиска по индексу используется токен пути $.warehouses.stock, а условие @.stock > 0 проверяется пост-фильтром на каждой найденной записи. Это эффективно, когда соответствующее поле присутствует у сравнительно малой части документов.

Результат:

sku_id
10
11

Поиск по категории и наличию атрибута

Условия можно комбинировать любыми операторами AND и OR. Поиск товаров в категории tools, у которых указано поле price:

SELECT sku_id
FROM products VIEW attrs_json_idx
WHERE JSON_VALUE(attrs, '$.category' RETURNING Utf8) = "tools"u
  AND JSON_EXISTS(attrs, '$.price');

Результат:

sku_id
10
12

Подробнее

• Поддерживаемые предикаты JSON-индекса — полные правила того, какие выражения индексируются.
• Обработка AND и OR — нюансы комбинирования условий, в том числе с неиндексируемыми предикатами.
• Параметризованные запросы и переменные JsonPath — параметризованные варианты этих же запросов.