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

Question

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}'"))

user330315 · Accepted Answer · 2022-08-11 09:49:06Z

1

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"'

answered Aug 11, 2022 at 9:49

user330315

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

1 Comment

geriwald Over a year ago

Great, it works, but without the search in the select, i.e. : SELECT jsonb_path_query_first(field, '$.items.*'). I didn't find that jsonb_path_query_first() command in the doc, it was the last one (postgresql.org/docs/12/functions-json.html)

Collectives™ on Stack Overflow

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

Bonus question : The same in SqlAlchemy

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Bonus question : The same in SqlAlchemy

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related