Flask SQLAlchemy Filter On A Postgres JSON List Object Based on a Single String-CodePudding

I have a model called Testing. The field called alias is a JSON field (a list really) and has values such as ["a", "b"] or ["d", "e"] and so on.

class Testing(db.Model):
    __tablename__ = 'testing'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(25))
    alias = db.Column(JSON)

    def __init__(self, name, alias):
        self.name = name
        self.alias = alias

In my flask view I grab a url parameter that I want to use to filter Testing to get all Testing objects in which the parameter value is in the alias json list. So for example if url_param_value="a" I want all the Testing objects where "a" is in alias. So the alias value of ["a", "b"] would be a hit in this example.

Here is my approach but its not working and I assume it has to do with seralization.

Testing.query.filter(Testing.alias.contains(url_param_val)).all()

I am getting the below error

NotImplementedError: Operator 'contains' is not supported on this expression

CodePudding user response：

The name field is a JSON type, not an array type. JSON columns don't have a contains method, even if you happen to be storing array data (how would the database know?)

In Postgres, you can use json_array_elements to expand a JSON array to a set of JSON values; this will return one row per element:

select id, json_array_elements(alias) as val from testing;

   id    |         val
--------- --------------------
 1       | "a"
 2       | "b"

You can use that as a subquery to select records that contain a matching value:

select t.id, t.name, t.alias, cast(q.val as varchar)
from testing t, (
    select id, json_array_elements(alias) as val
    from testing
) q
where q.id=t.id and cast(q.val as varchar) = '"a"';

In SQLAlchemy syntax:

subq = session.query(
    Testing.id,
    func.json_array_elements(Testing.alias).label("val")
).subquery()
q = session.query(Testing).filter(
    cast(subq.c.val, sa.Unicode) == '"a"',
    subq.c.id == Testing.id)

Warning: this is going to be very inefficient for large tables; you're probably better off fixing the types to match your data, and then creating appropriate indexes.