# Bug 3385: json filters

- Status: open
- Project: Zero
- Creator: @tantaman
- Assignee: @arv
- Labels: user-reported
- Created: 2025-01-03T13:04:09Z
- Modified: 2026-08-05T19:49:20Z
- Reactions: ➕️ ×9 (Abdullahi-abdiaziz, alamothe, andrictham, asharnadeem, asterikx, ccheever, jorroll, lsalling, q1b) · ⬆️ ×4 (alamothe, asharnadeem, asterikx, maksym-mikheienko) · 👍️ ×28 (Abdullahi-abdiaziz, Aexylus, MarkLyck, MilosNikolic, MoShizzle, TedisAgolli, TheAndrewJackson, abdelhameedhamdy, alamothe, asharnadeem, asterikx, austinm911, bitsoflogic, cbnsndwch, cloleb, danielcompton, darrenbarklie, elledienne, herkulano, jorroll, lsalling, m44rten1, malte-j, q1b, snehalbaghel, tangdrew, temarusanov, typedrat) · 🔥 ×8 (Abdullahi-abdiaziz, Rayyan-Nadeem, abdelhameedhamdy, alamothe, asharnadeem, asterikx, cbnsndwch, q1b)
- URL: https://bugs.rocicorp.dev/p/zero/issue/3385

## Description

https://discord.com/channels/830183651022471199/1324567341484347416/1324567346215518219

Allow accessing json values in `where` clauses'

Workaround: https://bugs.rocicorp.dev/issue/3385#comment-gOEXyIy0pSbBaIhN_IW8s

## Comments (11)

### @jorroll — 2025-01-03T20:08:39Z

+1 (there should really be a "me too" emoji... "👍" is too generic).

### @cbnsndwch — 2025-01-13T18:16:32Z

➕1️⃣ 😁

Reactions: 🙂‍↕️ ×2 (Abdullahi-abdiaziz, q1b)

### @asharnadeem — 2025-03-06T19:42:38Z

This would also be huge for permission checks where needed values are in a JSON

Reactions: 👍️ ×1 (arv)

### @bitsoflogic — 2025-03-16T14:01:49Z

As a workaround, I wonder if you could create a Postgres View to leverage the existing filtering capabilities. Something like this:

```
CREATE VIEW filterable_view AS 
SELECT json_field->>'x' as filter_on_this, * FROM table_with_json
```

### @arv — 2025-03-24T09:17:57Z

So views do not currently work. I think they do not generate replication messages but @darkgnotic can confirm.

### @aboodman — 2025-03-24T09:43:59Z

> I think they do not generate replication messages but @darkgnotic can confirm.

Confirmed: https://zero.rocicorp.dev/docs/postgres-support#object-types

### @aboodman — 2025-04-14T19:37:58Z

Here is a pattern I realized as a workaround, just [custom mutators](https://zero.rocicorp.dev/docs/custom-mutators):

- Use a trigger to extract the data in question to a separate column automatically.
- (optional) use a custom pg publication to exclude the JSON column if it's big and you don't want to sync it: https://zero.rocicorp.dev/docs/postgres-support#limiting-replication
- Use a custom mutator to support optimistic updates to the generated column. In the client-side mutator, modify the generated column. In the server-side mutator, modify the JSON column.

This can likely be extracted into a little helper library.

### @alamothe — 2025-10-27T19:21:49Z

Consider adding a generic `where("any SQL here")` clause which would cover JSON, COALESCE, IF and others. Could be done by parsing the expression and evaluating it on the client.

### @aboodman — 2025-10-27T19:23:55Z

> Consider adding a generic where("any SQL here") clause which would cover JSON, COALESCE, IF and others. Could be done by parsing the expression and evaluating it on the client.

Every supported SQL feature would have to be implemented one-by-one. There's not really a benefit to supporting the SQL syntax – the implementation is 90% of the effort.

### @alamothe — 2025-11-06T04:50:39Z

> Every supported SQL feature would have to be implemented one-by-one. There's not really a benefit to supporting the SQL syntax – the implementation is 90% of the effort.

It's not really necessary to support everything of course. Just the comparison operators (which Zero already supports), then binary operators +,-,*,/ and some functions like MAX, MIN, COALESCE, IF, and JSON functions.

I believe it could be done in a single PR.

### @elledienne — 2025-11-07T17:17:53Z

Had a use case today where I needed to be able to filter on a jsonb column 🥲