Postgresql SQLalchemy filter query for list of jsonb strings

Question

Please forgive me as I am new to SQLalchemy and still a beginner with Postgresql.

I have a gin indexed jsonb string column that is like below:

my_id| my_column
0    | "AAAA"
1    | "BBBB"
2    | "CCCC"

I need to search in 'my_column' for 'AAAA' and 'CCCC' as I only receive this string. This should be done preferable without a for-loop as there are hundreds of these strings. 'my_column' belongs to the table 'my_table'. The column 'my_id' is the primary key. The explicit sql query just for 'AAAA' would be:

select * from my_table
where my_column ? 'AAAA'

Using SQLalchemy, the query for this would be in python something like:

from sqlalchemy import create_engine, Column, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import JSONB

Base = declarative_base()

class my_class(Base):
__tablename__ = 'my_table'
my_id     =  Column(Integer, primary_key=True)
my_column =  Column(JSONB)

engine = create_engine('postgresql+psycopg2://user:pass@host/db')
session = sessionmaker(bind=engine)()

session.query(my_class).filter(my_class.my_column.has_key("AAAA").all()

I know that it is possible to query a list of integers using the in clause like below:

session.query(my_class).filter(my_class.example_id.in_((123,456))).all()

But I have not been successful using like so:

session.query(my_class).filter(my_class.my_column.in_(('AAAA','CCCC'))).all()

Is there a way to query a list of strings in a jsonb column without resorting to a loop? Is it possible to just input a list-like parameter containing all the strings without explicitly typing all the strings I want to search like so:

session.query(my_class).filter(my_class.my_column.in_(([list_full_of strings]))).all()

EDIT:

From the query:

session.query(my_class).filter(my_class.my_column.in_(('AAAA','CCCC'))).all()

The following error arises:

sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type json
LINE 3: WHERE my_table.my_column IN ('AAAA', 'CCCC')
                                        ^
DETAIL:  Token "AAAA" is invalid.
CONTEXT:  JSON data, line 1: AAAA...

[SQL: SELECT my_table.my_id AS my_table_my_id, my_table.my_column AS my_table_my_column
FROM my_table
WHERE my_table.my_column IN (%(my_column_1)s, %(my_column_2)s)]
[parameters: {'my_column_1': 'AAAA', 'my_column_2': 'CCCC'}]
(Background on this error at: http://sqlalche.me/e/13/9h9h)

Does the query with the strings return an empty value? Or does it throw an error? — Ruben Helsloot
– Ruben Helsloot, Commented Aug 18, 2020 at 11:44
I don't know how you got JSONB strings, but I am not sure whether that is ever the best way to store them. Why not just make it VARCHAR (sa.String)? — Ruben Helsloot
– Ruben Helsloot, Commented Aug 18, 2020 at 12:23
What do you mean? Is it actually a deeper object? Because that changes things — Ruben Helsloot
– Ruben Helsloot, Commented Aug 18, 2020 at 13:25

snakecharmerb · Accepted Answer · 2020-08-18 13:28:09Z

2

You could create the required has_key expressions inside an or_, like this:

keys = ['AAAA', 'CCCC'] 
clauses = [my_class.my_column.has_key(k) for k in keys]  
recs = session.query(my_class).filter(sqlalchemy.or_(*clauses)).all()  
print([r.my_column for r in recs])

Output:

['AAAA', 'CCCC']

answered Aug 18, 2020 at 13:28

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql SQLalchemy filter query for list of jsonb strings

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related