cockroachdb / cockroachdb/docs
Update inverted index docs
- Dominant language
- HTML
- Stars
- 212
- Forks
- 476
- Avg merge
- 40m
- Merged PRs (30d)
- 3
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:
```sql
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
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.