แบบสอบถามตัวกรอง Postgresql SQLalchemy สำหรับรายการสตริง jsonb

Aug 18 2020

โปรดยกโทษให้ฉันด้วยเนื่องจากฉันยังใหม่กับ SQLalchemy และยังเป็นมือใหม่กับ Postgresql

ฉันมีคอลัมน์สตริง jsonb ที่สร้างดัชนีจินที่เป็นดังนี้:

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

ฉันต้องการค้นหาใน "my_column" สำหรับ "AAAA" และ "CCCC" เนื่องจากฉันได้รับเฉพาะสตริงนี้ สิ่งนี้ควรทำได้ดีกว่าโดยไม่ต้องใช้ for-loop เนื่องจากมีสตริงเหล่านี้หลายร้อยสาย 'my_column' เป็นของตาราง 'my_table' คอลัมน์ 'my_id' เป็นคีย์หลัก แบบสอบถาม sql ที่ชัดเจนสำหรับ 'AAAA' จะเป็น:

select * from my_table
where my_column ? 'AAAA'

การใช้ SQLalchemy แบบสอบถามสำหรับสิ่งนี้จะอยู่ใน python เช่น:

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()

ฉันรู้ว่าเป็นไปได้ที่จะค้นหารายการจำนวนเต็มโดยใช้ in clause ดังต่อไปนี้:

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

แต่ฉันไม่ประสบความสำเร็จในการใช้สิ่งนี้:

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

มีวิธีค้นหารายการสตริงในคอลัมน์ jsonb โดยไม่ต้องใช้การวนซ้ำหรือไม่? เป็นไปได้ไหมที่จะป้อนพารามิเตอร์แบบรายการที่มีสตริงทั้งหมดโดยไม่ต้องพิมพ์สตริงทั้งหมดที่ฉันต้องการค้นหาอย่างชัดเจน:

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

แก้ไข:

จากแบบสอบถาม:

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

เกิดข้อผิดพลาดต่อไปนี้:

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)

คำตอบ

1 snakecharmerb Aug 18 2020 at 20:28

คุณสามารถสร้างhas_keyนิพจน์ที่ต้องการภายใน an or_เช่นนี้:

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])

เอาท์พุต:

['AAAA', 'CCCC']