Convert Postgresql query with jsonb_each to sqlalchemy

user_4371354 0
Viewed 166

I have a query which has two jsonb_each statements and I need to convert it to sqlalchemy. I have tried using subquery, aliases, and even the op fn to literally translate the query but it fails with an InternalError.

What I am specifically stuck at is I dont know how to access the {key: value} pairs that are returned from the first jsonb_each function.

this is what I have so far

v = column('value', type_=JSONB)
k = column('key', type_=JSONB)
polarity = v['polarity'].astext
q = db.session.query(
        db.func.count(polarity).label('count'),
        ## other fields
    )\
    .select_from(MyModel)\
    .join(db.func.jsonb_each(MyModel.json_content['myMap']).alias('items'), sa.true())\
    .join(
            # ... stuck here
            # I want to access the returned data from the previous join here
     )\
    # group by and order by here
    .all()

the query

-- myTable
--  - id
--  - json_content


SELECT
    count(d.value ->> 'polarity') as count,
    d.value ->> 'polarity' as polarity,
       d.key as key
from myTable t
join jsonb_each(t.json_content -> 'myMap') m on true
join jsonb_each((m.value -> 'data') - 'text') d on true
group by d.value ->> 'polarity', d.key;

the result set I want to get

count   polarity    category
----------------------------
1       positive    cate2
1       positive    cate4
2       negative    cate1
1       negative    cate2

the sample json object I am trying to query

{
    "myMap": {
        "0": {
            "data": {
                "text": "koolaid",
                "cate1": {
                    "polarity": "negative"
                },
                "cate2": {
                    "polarity": "positive"

                }
            }
        },
        "1": {
            "data": {
                "text": "some other text",
                "cate1": {
                    "polarity": "negative"
                },
                "cate2": {
                    "polarity": "negative"
                },
                "cate4": {
                    "polarity": "positive"
                }
            }
        }
    }
}

Let me know if any more information is required

sqlalchemy postgresql python
1 Answers

Instead of using column you can use aliased combined with the recipe defined here and query illustrated here

So the code would be something like:

first_alias = aliased(jsonb_each(MyModel.json_content['myMap'])))
second_alias = aliased(jsonb_each(first_alias.c.value.op("->")("data").op("-")("text")))
polarity = second_alias.c.value.op('->>')('polarity')
q = db.session.query(
    db.func.count(polarity).label('count'),
    ## other fields
)\
.select_from(MyModel)\
.join(first_alias, sa.true())\
.join(second_alias, sa.true())\
# group by and order by here
.all()

The jsonb_each function is not imported from Sqlalchemy functions. It is custom made as specified here

user_91372760
Related