Selecting data from PostgreSQL JSON field with SQLAlchemy / Flask SQLAlchemy-CodePudding

My JSON in the db is like this:

{
    "ingredients": [
        "3 tablespoons almond butter",
        ...
        "Lime wedges"
    ],
    "instructions": [
        "In a bowl" 
    ]
    "tags": [
        "gluten-free",
        "grain bowls",
        "Thai"
     ]
}

My app.py file:

class Recipe(db.Model):
    __tablename__ = "recipe"
    ....
    recipe = db.Column(db.JSON)

I'm trying, for starters, to select all records with the "Thai" tag.

Recipe.query.filter(Recipe.recipe['tags'].contains(["Thai"])).all()

tells me I might need explicit type casts, I've tried astext and cast.. nothing seems to be working. Not sure what I'm doing wrong.

here's the end of the stack trace:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) operator does not exist: json ~~ text
LINE 3: WHERE ((recipe.recipe -> 'tags') LIKE '%' || CAST('["%Thai%"...
                                         ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

[SQL: SELECT recipe.id AS recipe_id, recipe.url_id AS recipe_url_id, recipe.author AS recipe_author, recipe.description AS recipe_description, recipe.recipe AS recipe_recipe 
FROM recipe 
WHERE ((recipe.recipe -> %(recipe_1)s) LIKE '%%' || CAST(%(param_1)s AS JSON) || '%%')]
[parameters: {'recipe_1': 'tags', 'param_1': '["%Thai%"]'}]

With that I tried:

Recipe.query.filter(Recipe.recipe['tags'].contains(cast(["%Thai%"], JSON)))

What has worked is

db.engine.execute(text("select * from recipe where recipe->> 'tags' like '%Thai%'"))

I can live with that but am stubbornly wanting it to happen thru the ORM.

The only clue I have is that in the stack trace I'm seeing it generates a sql statement with -> not ->> ... but I can't figure how to get the ->>

Then I found this:

tags_subquery = db.session.query(func.json_array_elements(Recipe.recipe['tags']).label('tags')).subquery()
query = db.session.query(Recipe, tags_subquery.c.tags).filter(tags_subquery.c.tags.op('->>')('tags').like("%Thai%"))

And there's no error, but an empty result... I think I'm somewhat close now thanks to @r-m-n

CodePudding user response：

You can avoid the error by casting the left hand side of the expression to a text type:

# select cast('{"a": ["spam", "ham"]}'::json->'a' as text) like '%spam%' as "Exists?";
 Exists? 
═════════
 t

but this will also match substrings, which may not be desirable:

# select cast('{"a": ["spam", "ham"]}'::json->'a' as text) like '%am%' as "Exists?";
 Exists? 
═════════
 t

A better option would be to cast to jsonb:

# select cast('{"a": ["spam", "ham"]}'::json->'a' as jsonb) @> '["spam"]' as "Exists?";
 Exists? 
═════════
 t
 
# select cast('{"a": ["spam", "ham"]}'::json->'a' as jsonb) @> '["am"]' as "Exists?";
 Exists? 
═════════
 f

On the Python side, this would look like

from sqlalchemy import cast
from sqlalchemy.dialects.postgresql import JSONB

result = Recipe.query.filter(
    cast(Recipe.recipe['tags'], JSONB).contains(["Thai"])
).all()

Changing the column type to JSONB would remove the need for casting (but would require a migration):

class Recipe(db.Model)
    ...
    recipe = sb.Column(JSONB)

result = Recipe.filter(Recipe.recipe['tags'].contains(['Thai'])).all()

You can see how JSON(B) comparison methods map to Postgresql functions in the source.