Using SQLAlchey query top x items in a Postgres JSONB field

Viewed 7

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.

0 Answers
Related