I’m trying to use the
jsonb_array_elements Postgres function with querysets but I am running into issues.
I defined a custom
class JsonbArrayElements(Func): function = 'jsonb_array_elements'
While this does seem to result in proper sql when used in an annotation, the issue comes when I try to filter on the new field. Postgres doesn’t allow you to use the new field in a
where clause without using a subquery first.
The query that I am trying to write is equivalent to:
select * from ( select id, jsonb_array_elements(json_data->'some_array') as elem from foo as foo1 union select id, jsonb_array_elements(json_data->'other_array') as elem from foo as foo2 ) as foo_w_elems where (elem->>'subfield')::int in ( select id from bar where expires_at >= CURRENT_TIMESTAMP )
Unfortunately, even with Django’s subquery support, I have been unable to get a query to add the where clause to the results of a subquery.
My issue seems related to #24462 (Add a new QuerySet operation to use current results as a subquery) – Django, but was curious if there is a way to use and filter
jsonb_array_elements results in Django today without using raw sql?
I’m also investigating using the low-level query API directly, but have run into some issues so far.