JSON index
This page describes configuring the JSON index for Apache Pinot.
The JSON index can be applied to JSON string columns to accelerate value lookups and filtering for the column.
When to use JSON index
Use the JSON string can be used to represent array, map, and nested fields without forcing a fixed schema. While JSON strings are flexible, filtering on JSON string columns is expensive, so consider the use case.
Suppose we have some JSON records similar to the following sample record stored in the person
column:
Without an index, to look up the key and filter records based on the value, Pinot must scan and reconstruct the JSON object from the JSON string for every record, look up the key and then compare the value.
For example, in order to find all persons whose name is "adam", the query will look like:
The JSON index is designed to accelerate the filtering on JSON string columns without scanning and reconstructing all the JSON objects.
Enable and configure a JSON index
To enable the JSON index, you can configure the following options in the table configuration:
Recommended way to configure
The recommended way to configure a JSON index is in the fieldConfigList.indexes
object, within the json
key.
All options are optional, so the following is a valid configuration that use the default parameter values:
Deprecated ways to configure JSON indexes
There are two older ways to configure the indexes that can be configured in the tableIndexConfig
section inside table config.
The first one uses the same JSON explained above, but it is defined inside tableIndexConfig.jsonIndexConfigs.<column name>
:
Like in the previous case, all parameters are optional, so the following is also valid:
The last option does not support to configure any parameter. In order to use this option, add the name of the column in tableIndexConfig.jsonIndexColumns
like in this example:
Example:
With the following JSON document:
Using the default setting, we will flatten the document into the following records:
With maxLevels set to 1:
With maxLevels set to 2:
With excludeArray set to true:
With disableCrossArrayUnnest set to true:
With includePaths set to ["$.name", "$.addresses[*].country"]:
With excludePaths set to ["$.age", "$.addresses[*].number"]:
With excludeFields set to ["age", "street"]:
Note that the JSON index can only be applied to STRING/JSON
columns whose values are JSON strings.
To reduce unnecessary storage overhead when using a JSON index, we recommend that you add the indexed column to the noDictionaryColumns
columns list.
For instructions on that configuration property, see the Raw value forward index documentation.
How to use the JSON index
The JSON index can be used via the JSON_MATCH
predicate: JSON_MATCH(<column>, '<filterExpression>')
. For example, to find every entry with the name "adam":
Note that the quotes within the filter expression need to be escaped.
Supported filter expressions
Simple key lookup
Find all persons whose name is "adam":
Chained key lookup
Find all persons who have an address (one of the addresses) with number 112:
Find all persons who have at least one address that is not in the US:
Regex based lookup
Find all persons who have an address (one of the addresses) where the street contains the term 'st':
Range lookup
Find all persons whose age is greater than 18:
Nested filter expression
Find all persons whose name is "adam" and also have an address (one of the addresses) with number 112:
NOT IN
and !=
can't be used in nested filter expressions in Pinot versions older than 1.2.0. Note that IS NULL
cannot be used in nested filter expressions currently.
Array access
Find all persons whose first address has number 112:
Existence check
Find all persons who have a phone field within the JSON:
Find all persons whose first address does not contain floor field within the JSON:
JSON context is maintained
The JSON context is maintained for object elements within an array, meaning the filter won't cross-match different objects in the array.
To find all persons who live on "main st" in "ca":
This query won't match "adam" because none of his addresses matches both the street and the country.
If you don't want JSON context, use multiple separate JSON_MATCH
predicates. For example, to find all persons who have addresses on "main st" and have addresses in "ca" (matches need not have the same address):
This query will match "adam" because one of his addresses matches the street and another one matches the country.
The array index is maintained as a separate entry within the element, so in order to query different elements within an array, multiple JSON_MATCH
predicates are required. For example, to find all persons who have first address on "main st" and second address on "second st":
Supported JSON values
Object
See examples above.
Array
To find the records with array element "item1" in "arrayCol":
To find the records with second array element "item2" in "arrayCol":
Value
To find the records with value 123 in "valueCol":
Null
To find the records with null in "nullableCol":
Limitations
The key (left-hand side) of the filter expression must be the leaf level of the JSON object, for example,
"$.addresses[*]"='main st'
won't work.