Using jsonb_array_elements with querysets

Using Django
1

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 #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.

1 Like