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 '["user1", "user2"]'::jsonb ? 'user3'; ?column? -- f (1 row) SQLAlchemy surfaces the "?" operator as .has_key https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSONB.Comparator.has_key https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/dialects/postgresql/json.py#L33 Simon On Mon, Oct 18, 2021 at 10:35 PM Jonathan Vanasco wrote: > > 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 the column like text and > match/regex out a list value like `"user1"` - but that didn't work right. > > This type of search is possible with advanced PostgreSQL queries, by using > the functions like json_array_elements on a field and joining against that. > That's really not within the scope of SQLAlchemy or this list though, and > you'll have better luck search (or asking) on Stack Overflow. There are a > handful of questions and solutions there on this topic. > > Once you can figure out the PostgreSQL queries to accomplish what you want, > this list can help you convert it to SQLAlchemy if you have trouble. > > On Wednesday, October 13, 2021 at 9:50:16 AM UTC-4 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 with >> SqlAlchemy,like above that checks if the value "user1" is contained in this >> column? > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/8f1986f6-4a39-4cad-93f2-a8d1c392b4b2n%40googlegroups.com. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexfnFazzHYEgM%2BKt9QqcHM-HQFWkpjofvih%3Dm5GNL5G4dQ%40mail.gmail.com.
[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 the column like text and match/regex out a list value like `"user1"` - but that didn't work right. This type of search is possible with advanced PostgreSQL queries, by using the functions like json_array_elements on a field and joining against that. That's really not within the scope of SQLAlchemy or this list though, and you'll have better luck search (or asking) on Stack Overflow. There are a handful of questions and solutions there on this topic. Once you can figure out the PostgreSQL queries to accomplish what you want, this list can help you convert it to SQLAlchemy if you have trouble. On Wednesday, October 13, 2021 at 9:50:16 AM UTC-4 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 with > SqlAlchemy,like above that checks if the value "user1" is contained in this > column? > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/8f1986f6-4a39-4cad-93f2-a8d1c392b4b2n%40googlegroups.com.
[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 with
> SqlAlchemy,like above that checks if the value "user1" is contained in this
> column?
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/8ed928f8-e79c-4228-a5cc-942ce87f1a26n%40googlegroups.com.
