How to use postgresql's jsonb_each function with sqlalchemy's ORM

Question

I'm trying to write a function to calculate the average value of a nested json value in postgres via sqlalchemy. The value I'm trying to average is in a Statistics table, with a scores column that holds a json dictionary like this (filtered to the relevant structure): {1: {'score': 0.0}, 2: {'score': 0.0} ...}.

Written in postgres, the query looks like this:

SELECT *, avg((v->>'score')::float) AS average_score
FROM lms.statistics, jsonb_each(statistics.scores) js(k, v)
WHERE jsonb_typeof(scores) != 'null'
GROUP BY statistics.id

And I've cast it mostly into the following sqlalchemy code:

(
  session.query(Statistics)
  .add_columns(literal_column("avg((v->>'score')::float)").label('average_score'))
  .filter(literal("jsonb_typeof(statistics.scores != 'null'"))
  .group_by(Statistics.id)
).all()

However, no matter what I try to do, sqlalchemy simply won't allow me to include the jsonb_each this query depends on. I've even tried restructuring the query to use an explicit join, and sqlalchemy's .join won't accept literal_column, text, or any trickery with outer joins or specifying fake join conditions. I'm at the end of my rope trying to cheat this in, when there has to be an sqlalchemy standard to insert plaintext queries into FROM or JOIN statements.

Ilja Everilä · Accepted Answer · 2018-08-15 20:19:59Z

3

With functions returning scalars or sets of single columns you'd simply use func.something.alias('x') and column('x'). Unfortunately SQLAlchemy does not support aliasing the columns explicitly, so handling functions returning multi column composites is a bit trickier. In case of jsonb_each the default names are key and value, so you could use those:

v = column('value', type_=JSONB)
score = v['score'].astext.cast(Float)

session.query(Statistics,
              func.avg(score).label('average_score')).\
    select_from(Statistics,
                func.jsonb_each(Statistics.scores).alias()).\
    filter(func.jsonb_typeof(Statistics.scores) != 'null').\
    group_by(Statistics.id)

edited Aug 15, 2018 at 20:19

answered Aug 15, 2018 at 19:37

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nathan Hazzard Over a year ago

This works great! Unfortunately, with this error out of the way, I actually realized I was hoping to use this in a join condition. Because I'm actually adding to a previously defined query, and thus get the error where FROM is already defined. But.... this then causes sqlalchemy to throw this: "sqlalchemy.exc.NotSupportedError: (psycopg2.NotSupportedError) set-returning functions are not allowed in JOIN conditions". However, I know this isn't the case because I've written the query in postgres with a full outer join, and it works just fine. Any advice on this?

Ilja Everilä Over a year ago

First thought is that are you trying to put a set-returning function in the ON clause (instead of a lateral join)? What do you mean by "hoping to use this in a join condition"? Could you wrap the existing query as a subquery and join against that? It sounds like you might have the makings of a new question :P

Nathan Hazzard Over a year ago

Ah. Got it. For future readers, I was able to move it into a join like so: .outerjoin(func.jsonb_each(Statistics.scores).alias(), text('true'))

jess Over a year ago

thanks so much for this thread; really helped me out!

Collectives™ on Stack Overflow

How to use postgresql's jsonb_each function with sqlalchemy's ORM

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related