id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34667	JSONField: KT() on a related object produces invalid SQL	Yury	nobody	"Given these models:

{{{#!python
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:
{{{#!python
>>> 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:
{{{#!python
>>> 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:
{{{#!python
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.
"	Bug	closed	Database layer (models, ORM)	4.2	Normal	duplicate			Unreviewed	0	0	0	0	0	0