SQLAlchemy: Query a JSON column (PostgreSQL) containing array and counting matches

Question

I'm a bit new to SQLAlchemy and using JSON columns in db.

My db model

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)

My JSON structure

[{"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"}]

tried query-1

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

It returns nothing.

tried query-2

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

It returns only one ( since i'm comparing to one position )

But My goal is to count how many class B are there in the hazardrating (key). Note there will be different types of classes like Class A , Class B , class N.

Answer 1

You can use the postgres function json_array_elements to form a subquery which you can filter to retrieve the count of Class B hazard ratings:

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)

The filter is using the safety_data labeled column of the subquery: subq.c.safety_data. With .op we are using an custom operator ->> which will convert json to text. See the docs for more information about the operator.