Re: [sqlalchemy] Re: SqlAlchemy with Postgres: How can I make a query that checks if a string is in a list inside a json column

For what it's worth, I think the "?" operator would work for this with JSONB, but not with JSON: postgres=# select '["user1", "user2"]'::jsonb ? 'user1'; ?column? -- t (1 row) postgres=# select '["user1", "user2"]'::jsonb ? 'user2'; ?column? -- t (1 row) postgres=# select

[sqlalchemy] Re: SqlAlchemy with Postgres: How can I make a query that checks if a string is in a list inside a json column

I'm not sure, but AFAIK, this type of search isn't *easily* doable in PostgreSQL. The json and jsonb operators and functions are really targeting "object literals" style data, not lists. https://www.postgresql.org/docs/current/functions-json.html In the past, I think one could search against

[sqlalchemy] Re: SqlAlchemy with Postgres: How can I make a query that checks if a string is in a list inside a json column

Use .any(): session.query(Gizmo).filter(Gizmo.users.any('user1')) On Wednesday, October 13, 2021 at 11:50:16 PM UTC+10 chat...@gmail.com wrote: > Imagine a Postgres JSON column with values like below: > "["user1", "user2"]" > > Is there any way to query a postgres JSON (not JSONB) column