Contains query on arrays

With the new inverted index support on arrays https://www.cockroachlabs.com/docs/stable/array.html, is it possible to perform a contains query that will check if ANY item in an array is present in the table?

ie: I have a row with a column of ARRAY[‘sky’, ‘road’, ‘car’]

Can I issue a single query for

SELECT * FROM my_table WHERE arr_column @> ANY(ARRAY[‘sky’, ‘bird’, tree’];

will return the given row?

Is there an efficient way to do this without a joins table?

Without optimizer support for disjunctions on inverted indexes (currently missing), I think the best you can do is issue a UNION query:

SELECT * FROM my_table WHERE array_column @> ARRAY['sky'] UNION
SELECT * FROM my_table WHERE array_column @> ARRAY['bird']

We’d like to add disjunction support for inverted indexes, though, because this is admittedly not very ergonomic.

Thanks,
Jordan

I’d imagine it would be pretty similar to the WHERE indexed_column = ANY($1, $2, $3); for a scalar column

Is there a github issue tracking this, or a rough timeline on when this feature might be out?

Here’s the ticket: https://github.com/cockroachdb/cockroach/issues/47340

We’re very likely to get it finished by the 20.2 release, which will be released in about 6 months time. For now, you’ll have to use the workaround I mentioned, unfortunately.