JSONField: KT() on a related object produces invalid SQL

[Yury]

Given these models:

from django.db import models


class Artist(models.Model):
    data = models.JSONField()


class Painting(models.Model):
    author = models.ForeignKey(Artist, on_delete=models.CASCADE)

The following query will produce invalid SQL:

>>> Painting.objects.annotate(artist_val=KT("author__data__mykey"))
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedFunction: operator does not exist: bigint #>> text[]
LINE 1: ..."author_id", ("json_related_painting"."author_id" #>> ARRAY[...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

The full query produced is:

>>> str(Painting.objects.annotate(artist_val=KT("author__data__mykey")).query)
'SELECT "json_related_painting"."id", "json_related_painting"."author_id", ("json_related_painting"."author_id" #>> [\'data\', \'mykey\']) AS "artist_val" FROM "json_related_painting"'

The KT() expression results in applying PostgreSQL #>> operator to the foreign key field itself rather than the actual field of the other table.

My use case for using KT() as opposed to F() is the following:

Painting.objects.annotate(artist_val=KT("author__data__mykey")).filter(artist_val__contains="value")

...i.e. searching for records which contain a given string in the value of mykey attribute of the data JSON object stored on a related object; i.e. I need to use ->> or #>> PostgreSQL operators for getting the field value as text instead of json, whereas F() uses ->. I presume this is why KT() was made part of the public API.

[Natalia Bidart]

I believe this is a duplicate of #34594. As mentioned in that ticket, this is a documented behavior.