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 expression, and the checks are performed using the existing functions JSON_EXISTS and 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_EXISTSorJSON_VALUEfunctions 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 on a JSON column. This approach is designed for scalable search.
JSON search with a JSON index
The key idea of a JSON index is building an 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, 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:
{
"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
- find rows by a value in a path using JSON_VALUE or a filter predicate within
JSON_EXISTS.
When executing a query, the JSON index can be automatically used by the 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, 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
VIEWexpression, an error is returned.
For more information, see VIEW (JSON index).
Additional information:
- JSON indexes — an overview of supported capabilities, predicates, and limitations.
- JSON functions — reference on
JSON_EXISTS,JSON_VALUE, and the JsonPath language. - VIEW (JSON index) — syntax for queries with a JSON index.