VIEW (JSON index)

To execute a SELECT query on a row table with explicit use of a JSON index, use the VIEW expression:

SELECT ...
FROM documents VIEW json_idx
WHERE <предикат на основе JSON_EXISTS / JSON_VALUE>
ORDER BY ...

In the query example above, documents is the name of the table containing a column of type Json or JsonDocument, and json_idx is the name of the JSON index created on that column.

If the predicate is not supported for execution via a JSON index, a query with an explicit VIEW expression fails with a compile-time error. Without the VIEW expression, the optimizer cannot select this index for such a predicate, and the query will be executed by another method (for example, selecting a different index or performing a full scan of the base table).

Automatic index selection

If the WHERE predicate contains JSON_EXISTS / JSON_VALUE calls on an indexed JSON column, the optimizer may use the JSON index without an explicit VIEW. Automatic selection follows these rules:

• The JSON index is considered by the optimizer with the lowest priority — it is a fallback option that is selected only after other access methods have been ruled out.
• The JSON index is not selected if the query can already be served by a primary key or another, more specific secondary index.
• Explicit specification of VIEW overrides the optimizer's decision and forces index usage.
• All indexed subexpressions in a single query must refer to the same indexed JSON column. If predicates on different indexed columns are combined via AND / OR, automatic selection is not applied.

JSON_EXISTS

JSON_EXISTS(doc, jsonpath) checks for the existence of a path or value inside a JSON document:

SELECT id, payload
FROM documents VIEW json_idx
WHERE JSON_EXISTS(payload, '$.user.id');

The JSON index supports almost all JsonPath syntax, except for nested predicates and boolean expressions at the context object level ($) in JSON_EXISTS. A more complex example of a supported expression:

SELECT id, payload
FROM documents VIEW json_idx
WHERE JSON_EXISTS(payload, '$.user ? (@.id > 100)');

JSON_VALUE

JSON_VALUE(doc, jsonpath RETURNING ) extracts a scalar value by a JsonPath and returns it in the specified type. To use the JSON index, the RETURNING <type> clause is required:

SELECT id, payload
FROM documents VIEW json_idx
WHERE JSON_VALUE(payload, '$.user.name' RETURNING Utf8) = "Charlie"u;

When checking equality conditions or whether a value is in a given list (IN), the index search is performed by the 'path + value' token, which provides the highest selectivity. For other comparisons (!=, <, >=, BETWEEN, etc.), only the path token is used, and the final comparison accuracy is ensured by a post-filter.

Parameters

Three ways to pass query parameters are supported:

1. Direct comparison of the JSON_VALUE result with a parameter:

   DECLARE $id AS Int64;
   SELECT * FROM documents VIEW json_idx
   WHERE JSON_VALUE(payload, '$.owner_id' RETURNING Int64) = $id;
   

2. Checking whether the result is in a list of values:

   DECLARE $tags AS List<Utf8>;
   SELECT * FROM documents VIEW json_idx
   WHERE JSON_VALUE(payload, '$.tag' RETURNING Utf8) IN $tags;
   

3. Passing a parameter to JsonPath via the PASSING clause:

   DECLARE $v AS Int64;
   SELECT * FROM documents VIEW json_idx
   WHERE JSON_EXISTS(payload, '$.k ? (@ == $v)' PASSING $v AS v);
   

AND and OR combinations

JSON_EXISTS and JSON_VALUE on the same JSON column can be combined in a single WHERE using the AND and OR operators:

SELECT id, payload
FROM documents VIEW json_idx
WHERE JSON_VALUE(payload, '$.user.name' RETURNING Utf8) = "Charlie"u
  AND JSON_VALUE(payload, '$.user.id' RETURNING Int64) BETWEEN 100 AND 200;