docs icon indicating copy to clipboard operation
docs copied to clipboard

Update inverted index docs

Open mgartner opened this issue 2 years ago • 0 comments

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

mgartner avatar Oct 31 '23 15:10 mgartner