Sqlalchemy filter nested jsonb within arrays

Question

I have a Postgres JSONB field, with some nested arrays and other objects.

from sqlalchemy.dialects.postgresql import JSONB


class Family(db.Model):
   meta = db.Column(JSONB)


joes = Family(meta=[
    {
        "name": "Joe",
        "children": [
            {
                "name": "Jane"
            },
            {
                "name": "Kate"
            }
        ]
    },
    {
        "name": "Lisa",
        "children": [
            {
                "name": "Mary"
            },
            {
                "name": "David"
            }
        ]
    },
])

Is there a way to query all the kids with a certain substring in their names?

If I want to query for 'a' it should get me Mary, David, Kate, Jane.

I was thinking, maybe something like

Family.query.filter(
    Family.meta.contains([{"children": [{"name": func.contains("a")}]}])
)

There is, for example adapting this, but it helps in providing answers if you provide the models, what you've tried etc. to work with. — Ilja Everilä
– Ilja Everilä, Commented Feb 13, 2019 at 12:46
Thanks! I looked at it, but I'm not sure how can it be adapted for multiple nested array objects. I've added a dummy model, but I don't think that it depends on the model's structure. — vtemian
– vtemian, Commented Feb 13, 2019 at 13:00

Ilja Everilä · Accepted Answer · 2020-07-14 06:54:41Z

The trick is to unnest the array or arrays using jsonb_array_elements(), and then filter:

meta_value = literal_column('meta.value', type_=JSONB)
children_value = literal_column('children.value', type_=JSONB)

Family.query.\
    with_entities(children_value['name'].astext).\
    select_from(
        Family,
        func.jsonb_array_elements(Family.meta).alias('meta'),
        func.jsonb_array_elements(
            meta_value['children']).alias('children')).\
    filter(children_value['name'].astext.contains('a'))

Note the use of literal_column() to reference the values of the set returning function jsonb_array_elements().

Another option is to use jsonb_path_query() (introduced in version 12):

name = column('name', type_=JSONB)
Family.query.\
    with_entities(name.astext).\
    select_from(
        func.jsonb_path_query(
            Family.meta,
            '$[*].children[*].name').alias('name')).\
    filter(name.astext.contains('a')).\
    all()

Collectives™ on Stack Overflow

Sqlalchemy filter nested jsonb within arrays

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related