Allow querying JSONField with F objects

[Gabor Körber]

Currently, it is rather undocumented, that F Objects do not perform JSON Lookups.

I suggest to either introduce a similar Object that is capable, or similar solution to make this possible; at the very least however, I suggest to add this information to the documentation for JSONField in django docs.

I stumbled on this when I tried to rewrite a raw statement in the form of
SELECT fields FROM schema WHERE lower(unaccent(jsonfield->lookup->>data)) LIKE lower(searchterm) ...

and also stumbled on stackoverflow dealing with this issue: ​https://stackoverflow.com/questions/40623820/django-jsonfield-postgres-and-f-object-comparison

Note, that if lookups in F might not easily be adaptable, the stackoverflow question has a nice side effect of showing off a possible complementary solution: overwrite/extend __getitem__ for F objects to deal with json / subfield lookups.

--
I thought brainstorming about such would be benefitial,
If I have overseen an already existing workaround I am eager to get corrected.

[Gabor Körber]

Replying to g4borg:
Note that I do have found that the workaround with RawSQL('jsonfield->lookup->>data') works as replacement for the F() call, in my specific use case, but it results in a suboptimal solution imho.

[Tim Graham]

I'm not sure what the best resolution might look like. It might involve creating a custom express rather than using F objects. #24709 is a similar issue for ArrayField.

[Mani]

I agree with Tim Graham, Creating custom expression would be a better approach than using F objects.

I have written a customer expression for querying JSONField and it works!

class KeyTextTransformFactory:

    def __init__(self, key_name):
        self.key_name = key_name

    def __call__(self, *args, **kwargs):
        return KeyTextTransform(self.key_name, *args, **kwargs)

class JSONF(F):

    def resolve_expression(self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False):
        rhs = super().resolve_expression(query, allow_joins, reuse, summarize, for_save)

        field_list = self.name.split(LOOKUP_SEP)
        for name in field_list[1:]:
            rhs = KeyTextTransformFactory(name)(rhs)
        return rhs

It is necessary to include Cast in rhs,

Sample.objects.filter(jsonfield__lookup__value=Cast(JSONF('value'), IntegerField()))

[GwynBleidD]

In Django 2.2 using F object with dicts or arrays in JSON field doesn't throw an error, it just cuts off anything after field name, so the result is the whole JSON. This means that F object is aware of field type, so I don't see any reason why we need separate expressions for that.

[Uxío]

@GwynBleidD I see it as an issue that F() object is different from the regular field issue:

str(MyEvent.objects.values('arguments__to').query)
'SELECT ("myapp_myevent"."arguments" -> \'to\') FROM "myapp_myevent"'

If I use F() object:

str(MyEvent.objects.values(x=F('arguments__to')).query)
'SELECT "myapp_myevent"."arguments" AS "x" FROM "myapp_myevent"'

This way I'm not able to do casting on that field, for example:

str(MyEvent.objects.values(x=Cast(F('arguments__to'), DecimalField())).query)
'SELECT ("myapp_myevent"."arguments")::numeric(None, None) AS "x" FROM "myapp_myevent"'

So for example I cannot find a way to translate this simple query:

SELECT SUM((arguments -> 'to')::decimal) as my_sum FROM myapp_myevent;

[Mariusz Felisiak]

Duplicate of #31639.