docs
docs copied to clipboard
Update inverted index docs
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