I want to match a Subquery key with a JSONField in Django without RawSQL.

Using Django Using the ORM
1

I wanted to match a JSONField item with a key from another table and use Subquery to get the item.

The result is

ename = Subquery(Event.objects.filter(eid=RawSQL(“url_kwargs->>‘eid’”, [])).values('ename')[:1])

I’m working as follows.

As you can see, I have to use RawSQL.

If I think about it honestly

ename=Subquery(Event.objects.filter(eid=OuterRef('url_kwargs__eid')).values('ename')[:1])

I think it should be possible to use
operator does not exist: character varying = jsonb
but I get an error if the type does not match.

If the type is an error, then

ename=Subquery(Event.objects.filter(eid=Cast(OuterRef('arguments__eid'),CharField())).values('ename')[:1])

and Cast, the error is gone, but even if there is a matching record, the ename is not retrieved.
I checked around and found that casting a JSONField results in a string that is enclosed in double quotes ("), which prevents the match.

I gave up at this point and decided to implement it with RawSQL.
If anyone knows how to implement this without RawSQL, I would appreciate it if you could let me know.

Versions where this problem was observed
Python 3.12.3
django 5.1.5
PostgreSQL 16.6 (Ubuntu 16.6-0ubuntu0.24.04.1)

2

Welcome @mNemu !

Side Note: When posting code here, enclose the code between lines of three
backtick - ` characters. This means you’ll have a line of ```, then your code,
then another line of ```. This forces the forum software to keep your code
properly formatted.
If you just need to fence one line, you can use a single backtick at the beginning and end of the code fragment.
(I have taken the liberty of correcting your original posts. Please remember to do this in the future.)

1 Like