JSON index – quick start

This guide shows how to create a JSON index and run queries using the JSON_EXISTS and JSON_VALUE functions in YDB.

Create a table and a JSON index

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

The column type JsonDocument stores JSON in a compact binary format and is preferred for an indexed column. Alternatively, the type Json (text representation) can be used.

The primary key of the table must consist of a single column of an integer type (Uint64, Uint32, Int64, or Int32) — this is a current limitation of the JSON index implementation.

Add test data

UPSERT INTO documents (id, payload) VALUES
    (1, JsonDocument(@@{"user": {"id": 100, "name": "Alice"}, "active": true}@@)),
    (2, JsonDocument(@@{"user": {"id": 101, "name": "Bob"}, "active": false}@@)),
    (3, JsonDocument(@@{"user": {"id": 102, "name": "Charlie"}, "archived": true}@@));

Here, the @@...@@ construct is a multiline string literal, convenient for writing JSON without escaping quotes. The JsonDocument(...) function converts text into a value of type JsonDocument.

Filter by the presence of a path in the document

The JSON_EXISTS function checks whether a path specified by a JsonPath expression exists in the document.

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

Result:

id
1
2
3

The path token $.user.id is used for index search. The index returns the result without scanning the main table.

Selecting rows with a specific document field value

The JSON_VALUE function extracts a scalar value by JsonPath; to use the index, you must specify the return type in the RETURNING clause:

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

Result:

id
1

When checking equality, the token «path + value» ($.user.name = "Alice") is placed into the index, which ensures the highest selectivity.

Combination of conditions

Multiple calls JSON_EXISTS / JSON_VALUE on a single indexed JSON column can be combined using the AND and OR operators:

SELECT id
FROM documents VIEW json_idx
WHERE JSON_EXISTS(payload, '$.user.id')
  AND JSON_VALUE(payload, '$.active' RETURNING Bool);

Result:

id
1

See also

• JSON indexes — a complete overview of capabilities and limitations.
• VIEW (JSON index) — query syntax through VIEW.
• INDEX (CREATE TABLE) — syntax for creating a JSON index.
• Catalog with nested attributes — example of a product catalog with nested attributes.