SQLAlchemy:查询包含数组和计数匹配的JSON列(PostgreSQL)

Question

我对SQLAlchemy有点陌生，在db中使用JSON列。

我的db模型

from sqlalchemy.dialects.postgresql import JSON
    
class Traydetails(db.Model):
    
    id= db.Column(db.Integer, autoincrement=True, primary_key=True)
    traynumber = db.Column(db.String, unique=True)
    safety_data = db.Column(JSON)

我的JSON结构

[{"Formulation": "Form1", "Hazard Rating": "Class B"}, {"Formulation": "Form2", "Hazard Rating": "Class B"}, {"Formulation": "Form3", "Hazard Rating": "Class B"}, {"Formulation": "Form4", "Hazard Rating": "Class B"}, {"Formulation": "Form5", "Hazard Rating": "Class B"}, {"Formulation": "Form6", "Hazard Rating": "Class B"}, {"Formulation": "Form7", "Hazard Rating": "Class B"}, {"Formulation": "Form8", "Hazard Rating": "Class B"}, {"Formulation": "Form9", "Hazard Rating": "Class B"}, {"Formulation": "Form10", "Hazard Rating": "Class B"}]

尝试了查询-1

hazard = Traydetails.query.filter(Traydetails.safety_data['Hazard Rating'].astext == "Class B").count()

它什么也不回。

尝试了查询-2

hazard = Traydetails.query.filter(Traydetails.safety_data[0]['Hazard Rating'].astext.cast(Unicode) == "Class B").count()

它只返回一个(因为我在比较一个位置)

但我的目标是数一数有多少B级在危险(钥匙)。注有不同类型的类，如A类、B类、N类。

Answer 1

您可以使用postgres函数json_array_elements形成一个子查询，您可以筛选该子查询以检索Class B危险等级的计数：

from sqlalchemy import func
subq = session.query(func.json_array_elements(Traydetails.safety_data).label('safety_data')).subquery()

count = session.query(subq).filter(subq.c.safety_data.op('->>')('Hazard Rating') == 'Class B').count()
print(count)

filter使用子查询：subq.c.safety_data的safety_data标记列。对于.op，我们使用一个自定义操作符->>，它将把json转换为文本。有关运算符的更多信息，请参见医生们。