KT doesn’t work across foreign keys

[Roman Odaisky]

Assuming

class Parent(Model):
    data = JSONField()

class Child(Model):
    parent = ForeignKey(Parent)

the expressions

Parent.objects.annotate(x=F("data__some_string")).first().x
Parent.objects.annotate(x=KT("data__some_string")).first().x
Parent.objects.first().data["some_string"]

all return the same value. So far, so good. However, in the following F works but KT doesn’t:

Child.objects.annotate(x=F("parent__data__some_string"))
Child.objects.annotate(x=KT("parent__data__some_string"))

The problem is in the SQL that KT generates: ("parent"."id" #>> ARRAY['data','some_string']) AS "x", which, it seems, is the result of KeyTextTransform.from_lookup taking all the double-underscore-separated parts of the argument of KT to mean JSON lookups and not other possible lookups such as foreign keys.

Please fix by using the same mechanisms F uses in this case.

[Mariusz Felisiak]

The problem is in the SQL that KT generates: ("parent"."id" #>> ARRAY['data','some_string']) AS "x", which, it seems, is the result of KeyTextTransform.from_lookup taking all the double-underscore-separated parts of the argument of KT to mean JSON lookups and not other possible lookups such as foreign keys.

Thanks for the report, however, it works exactly as ​documented "You can use the double underscore notation in lookup to chain dictionary key and index transforms.", so it's not a bug but a new feature request. As far as I'm aware we don't want to complicate it, and you can annotate nested JSONField to make it work, e.g.

Child.objects.annotate(data=F("parent__data"), x=KT("data__some_string"))

You can start a discussion on DevelopersMailingList if you don't agree.

[Natalia Bidart]

I think #34667 is a duplicate of this one.