Using jsonb_array_elements with querysets

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.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/68017f88-bbcc-43be-96ae-c7a3cd802d0an%40googlegroups.com.