Параметризованные запросы и переменные JsonPath

В большинстве приложений входные данные запроса не подставляются в текст SQL, а передаются через параметры. Типичные варианты использования параметров для работы с JSON-индексами:

1. прямое сравнение результата JSON_VALUE с параметром;
2. проверка наличия результата в списке значений через выражение IN;
3. передача параметра в выражение JsonPath через секцию PASSING.

Подготовка

Ниже приведён пример таблицы, JSON-индекса и заполнения данных.

CREATE TABLE documents (
    id Uint64,
    payload JsonDocument,
    PRIMARY KEY (id),
    INDEX json_idx GLOBAL USING json ON (payload)
);

UPSERT INTO documents (id, payload) VALUES
    (1, JsonDocument(@@{
        "owner_id": 100,
        "tag": "active",
        "archived": false,
        "content": {"x": 1, "y": 1}
    }@@)),
    (2, JsonDocument(@@{
        "owner_id": 100,
        "tag": "draft",
        "archived": false,
        "content": {"x": 1, "y": 2}
    }@@)),
    (3, JsonDocument(@@{
        "owner_id": 101,
        "tag": "active",
        "archived": true,
        "content": {"x": 2, "y": 1}
    }@@)),
    (4, JsonDocument(@@{
        "owner_id": 102,
        "tag": "pending",
        "archived": false,
        "content": {"x": 2, "y": 2}
    }@@));

Прямое сравнение с параметром

Самый распространённый вариант — параметр подставляется как правый операнд сравнения, а левым операндом является вызов JSON_VALUE с явным RETURNING:

DECLARE $owner_id AS Int64;

SELECT id, payload
FROM documents VIEW json_idx
WHERE JSON_VALUE(payload, '$.owner_id' RETURNING Int64) = $owner_id
  AND JSON_EXISTS(payload, '$.archived ? (@ == false)');

В индекс попадает токен «путь + значение» ($.owner_id = $owner_id) — параметр учитывается как обычное значение. Это позволяет выполнить выборку с такой же селективностью, как и при сравнении с литералом.

Запуск с $owner_id = 100 вернёт строки 1 и 2.

Поиск по списку значений

Для поиска по нескольким значениям одного поля удобно использовать IN:

DECLARE $tags AS List<Utf8>;

SELECT id
FROM documents VIEW json_idx
WHERE JSON_VALUE(payload, '$.tag' RETURNING Utf8) IN $tags;

Поведение индекса различается в зависимости от того, литерал это или параметр:

• IN ("active"u, "pending"u) — список литералов превращается в OR нескольких токенов «путь + значение», по одному на каждое значение списка.
• IN $tags — для каждого значения параметра во время выполнения запроса формируется токен «путь + значение» ($.tag + значение из списка). На этапе компиляции значения параметра ещё неизвестны, поэтому в плане запроса (EXPLAIN) такое условие отображается как пара «путь + параметр» ({"path": "$.tag", "param": "$tags"}).

Параметром для IN может выступать любая коллекция скалярных значений: List<T>, Tuple<T, ...>, Dict<K, V> или Set<T>.

Запуск с $tags = ["active"u, "pending"u] вернёт строки 1, 3 и 4.

Параметры внутри JsonPath (PASSING)

Если параметр должен использоваться внутри фильтра JsonPath (? (...)), его передают в секции PASSING:

DECLARE $min_stock AS Int64;

SELECT id
FROM documents VIEW json_idx
WHERE JSON_EXISTS(
    payload,
    '$.content ? (@.y > $threshold)'
    PASSING $min_stock AS threshold
);

Для поиска по индексу используется токен пути $.content.y, а условие @.y > $threshold проверяется пост-фильтром.

Аналогично PASSING работает в JSON_VALUE:

DECLARE $v AS Int64;

SELECT id
FROM documents VIEW json_idx
WHERE JSON_VALUE(
    payload,
    '$.content ? (@.y == $val)'
    PASSING $v AS val
    RETURNING Int64
) = 10;

Поддерживаемые типы параметров

Для всех трёх способов поддерживаются параметры со следующими типами: Int8 … Int64, Uint8 … Uint64, Float, Double, Bytes (String), Text (Utf8), Bool. Опциональные типы (Optional<T>) в параметрах не поддерживаются.

Подробнее о типах см. в JSON_VALUE.

Подробнее

• JSON-индекс — быстрый старт — базовый сценарий использования JSON-индекса.
• Передача параметров в предикаты JSON-индекса — детальное описание всех вариантов.
• JsonPath — синтаксис языка JsonPath.