Skip to content

Update inverted index docs #18027

Open
Open
@mgartner

Description

@mgartner

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

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions