Cockroach: sql: allow ARRAYs in forward indexes

[justinj]

Arrays support inverted indexes, but not forward indexes. If you want to index arrays, starting in CockroachDB 20.1 consider using an inverted index on your column.

This issue tracks adding forward indexes to array types.

[bra-fsn]

You should definitely implement it. :)

[bra-fsn]

Any chance that this will be implemented?

[justinj]

It's not currently planned for a release, it's on my to-do list to look into when I get some free time - can't promise anything at the moment though.

Out of curiosity, do you have any details on what you would use this for?

[bra-fsn]

Sure. Currently I have two use cases:

1. tagging messages with labels (like important, later etc) and searching for messages which have a label or labels. Labels here are mostly strings or enums (a limited set which can grow in time).
2. tagging objects with location IDs. There can be a limited (but variable in the range of 1 to tens) number of locations for each object. Some have only one, some more. The location IDs here are numbers (integers). I want to search for objects which live on a given location ID. There can be millions-billions of them, so I want to page on the result set (of course maintaining consistency, so between the start and the end of the search, the result set should be consistent, fixed)

[ckome]

You should definitely implement it. :)

[mzimmerman]

My use case for this is storing all email addresses associated with each email I load into the DB. I want to be able to search for all messages associated with one email address.

[jordanlewis]

Arrays are now indexable via inverted indexes, which supports containment operations on the arrays. We still don't support ordinary forward indexes on arrays.

[knz]

@rohany @RaduBerinde I think the recent key encoding for arrays will solve this issue?

(sorry I put this comment on an unrelated issue about JSON - this is the one about arrays)

[rohany]

Yup, I've marked that PR as closing this (#48045)