CODEWITHSUNDEEP
jsonpostgresqlsqlalchemyjsonpath
geriwald
geriwald

Reputation: 360

Retrieve a jsonb element with a jsonpath predicate in postgresql with sqlalchemy

I want to perform a search inside a JSONb field of datatable.

My JSON structure is something like :

"items": {
    "0": {
        "foo": {},
        "date": "2022-08-10T11:17:39"
    },
    "1": {
        "foo": {},
        "date": "2022-08-12T12:08:12"
    }
}

I managed to understand the SQL/JSON Path language enough to write the search predicate :

SELECT field->'items'
FROM datatable
WHERE field @@ '$.item.*.date == "2022-08-10T11:17:39"'

This returns the correct rows.

How do i modify this request to get only the item I'm looking for (i.e. datatable.field->items->0) ?

Of course, the work around is a simple for loop within the json, but I get the feeling I'm very close

Bonus question : The same in SqlAlchemy

The below query filter works as intended, but of course I get the complete json not the requested item :

time_str = f'"{time}"'
query = session.query(datatable.field)    
query = query.filter(text(f"field @@ '$.items.*.date == {time_str}'"))

Upvotes: 0

Views: 650

Answers (1)

user330315
user330315

Reputation:

To extract the item from the JSON, you can use a JSON path in the SELECT list:

SELECT jsonb_path_query_first(field, '$.items.* ? (@.date == "2022-08-12T12:08:12")')
FROM datatable
WHERE field @@ '$.items.*.date == "2022-08-10T11:17:39"'

Upvotes: 1

Related Questions