Open
Description
Marcus Gartner (mgartner) commented:
The [inverted index docs](https://www.cockroachlabs.com/docs/stable/inverted-indexes#arrays] do not list all operators for which inverted indexes on ARRAYs and JSON can be used.
Arrays
The following operators in filters can use inverted indexes on an ARRAY-type column:
- is contained by
<@
(already mentioned in docs) - contains
@>
(already mentioned in docs) - overlaps
&&
- Do the two arrays have any elements in common?
- Ex:
array_col && ARRAY['foo', 'bar')
JSON
- is contained by
<@
(already mentioned in docs) - contains
@>
(already mentioned in docs) - equals
=
(already mentioned in docs) - exist
?
- Does the string exist as a top-level key within the JSON value?
- Ex:
json_col ? 'foo'
- some/any exists
?|
- Do any of the strings in the array exist as top-level keys?
- Ex:
json_col ?| ARRAY['foo', 'bar']
- all exists ?&'
- Do all of the strings in the array exist as top-level keys?
- Ex:
json_col ?& ARRAY['foo', 'bar']
It might be worth noting that some more complex JSON expressions with the fetch value operator, ->
, can also be index-accelerated. Some examples:
SELECT * FROM t WHERE json_col->'foo' = '123'::JSON;
SELECT * FROM t WHERE json_col->0->'foo' = '123'::JSON;
SELECT * FROM t WHERE json_col->'foo' @> '[1, 2, 3]'::JSON;
SELECT * FROM t where json_col->'foo' IN ('[1, 2, 3]'::JSON, '{"a": "b"}'::JSON);
Jira Issue: DOC-9133