---
metadata:
  - name: generator
    content: Diplodoc Platform v5.57.3
alternate:
  - https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/query_execution/json_search.md
  - https://ydb-platform--ydb.viewer.diplodoc.com/ru/concepts/query_execution/json_search.md
  - href: https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/query_execution/json_search.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/concepts/query_execution/json_search.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb-platform--ydb.viewer.diplodoc.com/en/llms.txt

# Searching JSON document contents

JSON search is a way to find table rows by the content of a JSON document stored in a column of type `Json` or `JsonDocument`: by the existence of a path in the document and by the value located at the specified path. The path is specified using a [JsonPath](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/builtins/json.md#jsonpath) expression, and the checks are performed using the existing functions [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). Typical scenarios:

* Search for documents with a specific nested field
* Search for documents whose field at a given path equals the required value.
* filtering semi-structured data without a pre-defined schema.

In YDB, JSON search can be performed in two main ways:

* Without an index: scanning the table and applying the `JSON_EXISTS` or `JSON_VALUE` functions to each row. The approach is simple but scales poorly: the amount of scanning work grows with the table size.
* with a JSON index — creating a [JSON index](https://ydb-platform--ydb.viewer.diplodoc.com/en/dev/json-indexes.md) on a JSON column. This approach is designed for scalable search.

## JSON search with a JSON index {#json-search-index}

The key idea of a [JSON index](https://ydb-platform--ydb.viewer.diplodoc.com/en/dev/json-indexes.md) is building an [inverted index](https://en.wikipedia.org/wiki/Inverted_index) over paths and "path + value" pairs. Each path of a JSON document (and for equality checks, the scalar value at that path) is encoded into a token. For each token, the index stores a list of primary key values of the corresponding table rows. Queries to the index are reduced to an inverted search over these tokens — following the same scheme as a [full-text index](https://ydb-platform--ydb.viewer.diplodoc.com/en/dev/fulltext-indexes.md#basic), but with its own JSON tokenizer.

The JSON index does not store a copy of the entire JSON document, but decomposes it into individual tokens:

* For each path in the JSON tree, a token 'path exists' is created.
* if the path leads to a scalar value (string, number, boolean, or `null`), a «path + value» token is additionally created.

Arrays are transparent in this case: array elements are indexed under the same path as the array itself, so the index responds equally to queries to `$.items`, `$.items[0]`, and `$.items[*]`.

For example, for a document:


```json
{
    "id": 42042,
    "name": "Michael",
    "email": null,
    "items": [null, "str"],
    "parts": {
        "key": "k1",
        "value": false
    }
}
```


Conceptually, the following tokens are indexed (without regard to the specifics of the internal representation):

| Token | What it finds |
| --- | --- |
| `$` | document is not equal to `NULL` |
| `$.id` / `$.id == 42042` | path `id` exists / equals `42042` |
| `$.name` / `$.name == "Michael"` | path `name` exists / equals `"Michael"` |
| `$.email` / `$.email == null` | path `email` exists / equals `null` |
| `$.items` / `$.items == null` / `$.items == "str"` | path `items` exists / contains element `null` / contains element `"str"` |
| `$.parts` | path `parts` exists |
| `$.parts.key` / `$.parts.key == "k1"` | nested path exists / equals `"k1"` |
| `$.parts.value` / `$.parts.value == false` | nested path exists or equals `false` |

JSON index allows:

* find rows by path existence using [JSON_EXISTS](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/builtins/json.md#json_exists)
* find rows by a value in a path using [JSON_VALUE](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/builtins/json.md#json_value) or a filter predicate within `JSON_EXISTS`.

When executing a query, the JSON index can be automatically used by the [optimizer](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/glossary.md#optimizer). It is enough to write a regular condition `WHERE` with expressions `JSON_EXISTS` or `JSON_VALUE` on an indexed JSON column. The optimizer recognizes such a predicate and applies reading by the JSON index instead of scanning records of the source table. Additionally, the JSON index, like any other [secondary index](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/glossary.md#secondary-index), can be forcefully used by specifying its name in the section `VIEW IndexName`.

If the predicate cannot be converted into an index access, the behavior of YDB depends on whether the required JSON index was explicitly specified in the query:

* When the optimizer automatically selects an index, the query is simply executed without index acceleration (the result remains correct).
* When explicitly specifying an index via the `VIEW` expression, an error is returned.

For more information, see [VIEW (JSON index)](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/syntax/select/json_index.md).

Additional information:

* [JSON indexes](https://ydb-platform--ydb.viewer.diplodoc.com/en/dev/json-indexes.md) — an overview of supported capabilities, predicates, and limitations.
* [JSON functions](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/builtins/json.md) — reference on `JSON_EXISTS`, `JSON_VALUE`, and the JsonPath language.
* [VIEW (JSON index)](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/syntax/select/json_index.md) — syntax for queries with a JSON index.