# Bug 3617: Support postgres array types

- Status: open
- Project: Zero
- Creator: @aboodman
- Assignee: @arv
- Labels: user-reported
- Created: 2025-03-10T19:37:01Z
- Modified: 2025-09-05T22:07:15Z
- Reactions: 👍️ ×15 (GeorgeDettmer, MadeInPierre, TheAndrewJackson, carlosbensant, cbnsndwch, cloleb, dolphorama, edwardrenton, fforres, gabrielromk, julienroubieu, l0g1x, miguelrk, typedrat, x-sss-x)
- URL: https://bugs.rocicorp.dev/p/zero/issue/3617

## Description

We get fairly frequent requests for this.

Some users also need `IN`.

User reports:

https://discord.com/channels/830183651022471199/1368383182864646235

I think for V1 of operators we should support:

- json path extraction: `where("foo.bar[0].baz", "hotdog")`
  - `where()` will not ever use an index in this case for v1 - this will always be a filter
- containment: `where("foo", "contains", 42)`
  - works the same was as Postgres containment operator: https://www.postgresql.org/docs/current/datatype-json.html#JSON-CONTAINMENT
  - note this is also basically the same as TypeScript's `subtype` test, if we treat both operands as `as const`
  - this can double as `IN` for array inclusion checks

## Comments (8)

### @grgbkr — 2025-03-17T20:54:38Z

Sqlite doesn't have array type, so we'd probably represent this as JSON on the replica.
Would need to implement IN filtering in TableSource and MemorySource.

### @elledienne — 2025-05-10T15:07:39Z

Would love to see support for arrays 👀
For new tables it's easy for us to use JSON instead, but for existing ones that are already being used by our non-zero part of the app it's inconvenient to have to migrate them

### @arv — 2025-05-16T08:42:09Z

We will treat PG arrays as json in the zero schema.

Once we add support for JSON operators those need to also work on pg arrays in z2s/zero-pg.

### @aboodman — 2025-06-29T01:33:17Z

Initial array support is available in 0.21 but there is no operator support yet. Just syncing the arrays.

Reactions: 🔥 ×1 (elledienne)

### @aboodman — 2025-06-29T01:33:40Z

Leaving this bug open until we add initial operators.

### @gabrielromk — 2025-08-05T19:36:26Z

Are you planning to add support for the initial operators anytime soon?

We’re increasingly running into the limitation of not being able to check if an array contains a specific value — for example:

z.query.tasks.where('tags', 'CONTAINS', 'urgent').

Reactions: 👀 ×1 (miguelrk)

### @aboodman — 2025-08-07T08:36:44Z

Added proposal for initial operators to bug description.

### @0xcadams — 2025-08-27T19:12:54Z

I am noticing a few issues with the array types:
1. Boolean arrays fail with the error `MutationFailed: PostgresError: column \"bool_array\" is of type boolean[] but expression is of type boolean`
2. JSONB arrays (I know, a bit silly) do not decode JSON and end up as string