Feature request: index-backed containment queries on array attributes

[daveycodez]

Summary

There's no way to ask "which entities have X in their array attribute" such that the query uses an index. Both documented workarounds (#1203) break down at moderate scale, and Postgres supports exactly this natively — so this may be more about exposing existing substrate than building new machinery.

Current state

i.json<string[]>() stores arrays, but:

• No containment operator. Available operators are $in, $ne, $not, $gt, $lt, $gte, $lte, $like, $ilike, $isNull. $in is OR-shorthand over a scalar, not element containment.
• No array value type. i.string / number / boolean / date / json / any, so a text[] attribute can't be declared.
• Indexing a json array attribute is accepted: $ilike against an unindexed json attr fails with The attribute must be indexed to use comparison operators, and .indexed() on a json attr pushes cleanly. But it doesn't survive real array sizes (below).

The workarounds, and where they break

Per #1203 the guidance is (a) extract the array into its own namespace, or (b) store a delimited searchable string and use $like.

Concrete numbers from our app — search-result rows, each holding an array of card names:

• 913 rows, 134,473 total (row, element) pairs, mean 147 elements/row, median 56, p90 455, max 1,735.

(a) Extract to its own namespace does give a proper indexed equality lookup, but it costs ~134k rows today (~1.3M at our growth target), and up to 1,735 row writes when a single parent row is written — plus diffing on every rewrite, since the array is regenerated wholesale rather than appended to. That's heavy write amplification on a hot path.

(b) Delimited string + $like — resultsSearch: "|a|b|c|" matched with $ilike '%|a|%' — is cheap to write, but the pattern has a leading wildcard, so it can't use a btree index and degrades to a namespace scan on every lookup. Its cost grows linearly with corpus size, which is the exact axis we're scaling.

Storing the array as a single indexed string also hits a value-size ceiling: our largest row is ~35KB serialized, well past the ~2704-byte btree entry limit.

So today the choice is between a scan that scales with the corpus and a row-count/write-amplification explosion.

The ask

First-class, index-backed element containment. Either:

1. A containment operator on array-valued attributes — e.g. where: { results: { $has: "Sol Ring" } } — backed by a GIN index; or
2. A real array value type (i.array(i.string())) that can be .indexed(), with @> semantics.

For reference, in Postgres this is:

CREATE INDEX ON items USING GIN (results);
SELECT * FROM items WHERE results @> ARRAY['Sol Ring'];

Exact, index-backed element matching — and because GIN indexes each element separately, array size stops mattering, which is precisely the limit that blocks indexing the serialized forms today.

Happy to test a prototype against a real dataset if that's useful.