Using jsonb_array_elements with querysets

[Jameel A.]

I’m trying to use the jsonb_array_elements Postgres function with querysets but I am running into issues.

I defined a custom Func as:

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 https://code.djangoproject.com/ticket/24462, 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.