I have data stored in a jsonb field like so:
class Test(Base):
__tablename__ = 'test'
id = Column(Integer, primary_key=True)
data = Column(JSONB)
In the data column there is a json of the form:
{depth: [0.0, 0.01, 0.02, 0.03, 0.04, 0.05, 0.06]}
I want to determine the max depth for each record and came up with the following query in raw SQL which does the job:
SELECT test.id, test.name,
(SELECT max(elem::float)
FROM jsonb_array_elements_text(test.data -> 'depth') As elem
) AS maxdepth
FROM test
ORDER BY maxdepth DESC
As I am using SQLAlchemy ORM in my application I want to write this query with SQLAlchemy ORM, but I cannot come up with the proper form.
I was thinking I need something like this:
subq = session.query(
func.max().label('maxdepth')).\
select_from(func.jsonb_array_elements(Test.data['depth'])).\
subquery()
stmnt = session.query(
Test.id, subq.c.maxdepth).\
order_by(subq.c.maxdepth)
But this obviously doesn't work as I don't know how to query from the fields extracted by jsonb_array_elements
