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+unsubscr...@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.
