---
metadata:
  - name: generator
    content: Diplodoc Platform v5.57.3
alternate:
  - https://ydb-platform--ydb.viewer.diplodoc.com/en/recipes/json-search/json-index-quickstart.md
  - https://ydb-platform--ydb.viewer.diplodoc.com/ru/recipes/json-search/json-index-quickstart.md
  - href: https://ydb-platform--ydb.viewer.diplodoc.com/en/recipes/json-search/json-index-quickstart.md
    type: text/markdown
    title: Markdown version
  - href: https://ydb-platform--ydb.viewer.diplodoc.com/en/llms.txt
    type: text/markdown
    title: llms.txt
sourcePath: en/core/recipes/json-search/json-index-quickstart.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb-platform--ydb.viewer.diplodoc.com/en/llms.txt

# JSON index – quick start

This guide shows how to create a [JSON index](https://ydb-platform--ydb.viewer.diplodoc.com/en/dev/json-indexes.md) and run queries using the [JSON_EXISTS](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/builtins/json.md#json_exists) and [JSON_VALUE](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/builtins/json.md#json_value) functions in YDB.

## Create a table and a JSON index


```yql
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](https://ydb-platform--ydb.viewer.diplodoc.com/en/dev/json-indexes.md#limitations) of the JSON index implementation.

## Add test data


```yql
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](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/syntax/expressions.md), 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](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/builtins/json.md#json_exists) function checks whether a path specified by a JsonPath expression exists in the document.


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


Result:


```text
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](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/builtins/json.md#json_value) function extracts a scalar value by JsonPath; to use the index, you must specify the return type in the `RETURNING` clause:


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


Result:


```text
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:


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


Result:


```text
id
1
```


## See also

* [JSON indexes](https://ydb-platform--ydb.viewer.diplodoc.com/en/dev/json-indexes.md) — a complete overview of capabilities and limitations.
* [VIEW (JSON index)](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/syntax/select/json_index.md) — query syntax through `VIEW`.
* [INDEX (CREATE TABLE)](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/syntax/create_table/json_index.md) — syntax for creating a JSON index.
* [Catalog with nested attributes](https://ydb-platform--ydb.viewer.diplodoc.com/en/recipes/json-search/json-index-catalog.md) — example of a product catalog with nested attributes.