Querying TEXT column containing valid JSON using SqlAlchemy

Question

I wish to query a valid json placed in a column of type TEXT(not JSON).

E.g. I wish to something like that:

records = db_session.query(Resource).filter(
              Resources.data["lastname"] == "Doe"
          ).all()

What should I do if the data column is of type TEXT?

snakecharmerb · Accepted Answer · 2022-10-29 10:11:53Z

1

You can cast the column to JSON (or JSONB if you are using PostgreSQL) to enable JSON operations:

records = db_session.query(Resource).filter(
              db.cast(Resources.data, db.JSON)["lastname"] == "Doe"
          ).all()

alternatively, use the column's cast method:

records = db_session.query(Resource).filter(
              Resources.data.cast(db.JSON)["lastname"] == "Doe"
          ).all()

answered Oct 29, 2022 at 10:11

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

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

1 Answer 1