SqlAlchemy search from values of jsonb objects, postgresql

Question

I have a jsonb field in a table having values of the form

from sqlalchemy.dialects.postgresql import JSONB

class Product(db.Model):
    __tablename__ = 'tbl_product'
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(JSONB, nullable=False, index=True, unique=True)
    slug = db.Column(JSONB, nullable=False, index=True, unique=True)

And I have product names in different languages:

name = {'en': 'Notebook', 'de': 'Notizbuch', ..... }

I want to make search from column name but I don't know in which language it appears, here I can search on specific language:

def get_product_by_name(name, lang):
    q = Product.query
    q = q.filter(Product.slug[lang].as_string() == slug)

    return q.first()

1Q: How can I search without language information? 2Q: Can I order product with language? Somethign like this:

def get_products(lang):
    q = Product.query
    q = q.order_by(Product.name[lang].asc())

    return q.all()

related: https://stackoverflow.com/q/43739601/2144390 – Gord Thompson Aug 13 '22 at 16:42 — Gord Thompson, Aug 13 '22 at 16:42

ljmc · Answer 1 · 2022-08-13T17:07:19.837

After @GordThompson's comment, I improved the query to use jsonb_each_text, it became a lot more straigtforward.

stmt = (
    select(Product)
    .join(func.jsonb_each_text(Product.name).alias("e"), onclause=True)
    .filter(text("e.value = :target_value")
)

For reference, here is the documentation for the JSON-functions of PostgreSQL.

Full script demo:

import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

metadata_obj = sa.MetaData()

t = sa.Table(
    "t73345077",
    metadata_obj,
    sa.Column("id", sa.Integer, primary_key=True),
    sa.Column("name", postgresql.JSONB),
)

engine = sa.create_engine(
    "postgresql+psycopg2://postgres:postgres@localhost:5432/postgres",
    echo=True,
    future=True,
)

metadata_obj.create_all(engine)

with engine.begin() as con:
    con.execute(
        sa.insert(t),
        [
            {"name": {"en": "goat", "fr": "chevre"}},
            {"name": {"en": "cow", "fr": "vache"}},
            {"name": {"en": "sheep", "fr": "mouton"}},
        ],
    )

with engine.connect() as con:
    r = con.execute(
        sa.select(t)
        .join(sa.func.jsonb_each_text(t.c.name).alias("e"), onclause=True)
        .filter(sa.text("e.value = :target_value"),
        dict(target_value="vache"),
    )
    print(r.all())  # gives [(2, {'en': 'cow', 'fr': 'vache'})]

The query emits the following:

SELECT t73345077.id, t73345077.name 
FROM t73345077 JOIN jsonb_each_text(t73345077.name) AS e ON true 
WHERE e.value = 'vache'

For posterity, I am keeping are the other queries I found that worked, but did not use bind params, so use the query above.

sa.select(t).filter(
    sa.func.jsonb_path_exists(
        t.c.name,
        '$.keyvalue().value ?(@ == "vache")'
    )
)

sa.select(t).filter(sa.text("name @? '$.** ? (@ == \"vache\")';"))

SqlAlchemy search from values of jsonb objects, postgresql

1 Answers1