Django JSONField query error: operator does not exist: jsonb = bigint

Question

Let's say I have 2 tables A and B. Table B has a JSON field named preferences which contains a field with id of table A called a_id.

I want to count number of B rows which refers to A table rows like this:

A.objects.annotate(count=Count(B.objects.filter(preferences__a_id=OuterRef('id'))))

However, I get an error that operator does not exist: jsonb = bigint. According to this answer, I should be able to refer to JSON field attributes using two underscores (__) between names. Why doesn't it work in my case?

Also, below the error there is a message:

HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

But I don't understand to what it actually refers to. I tried casting OuterRef(id) to integer through Cast() expression but that does not make any difference.

Answer 1

You have to explicitely cast preferences.a_id to an explicit type, because it is a jsonb on PostgreSQL not an int

solution

from django.db import models
from django.db.models.functions.comparison import Cast

A.objects.annotate(count=Count(B.objects.annotate(casted_id=Cast("preferences__a_id", models.IntegerField())).filter(casted_id=OuterRef('id'))))

used Django 4.1