I have a model with a JSONB field (Postgres).
from sqlalchemy.dialects.postgresql import JSONB
class Foo(Base):
__tablename__ = 'foo'
data = Column(JSONB, nullable=False)
...
where the data field looks like this:
[
{
"name": "a",
"value": 0.0143
},
{
"name": "b",
"value": 0.0039
},
{
"name": "c",
"value": 0.1537
},
...
]
and, given a search_name I want to return the rows where name is in the top x ranked names.
I know I can access the fields with something like:
res = session.query(Foo).filter(Foo.data['name'] == search_name)
but how do I order the JSON and extract the top names from that?
A SQLAlchemy solution would be preferred, but also a plain SQL one I can use as raw is fine.