Opened 23 months ago

Last modified 15 months ago

#29769 assigned New feature

Allow querying JSONField with F objects

Reported by: Gabor Körber Owned by: Mani
Component: contrib.postgres Version:
Severity: Normal Keywords: F JSONField lookup
Cc: Srinivas Reddy Thatiparthy Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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:

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.

Change History (8)

comment:1 Changed 23 months ago by Gabor Körber

Description: modified (diff)

comment:2 in reply to:  1 Changed 23 months ago by 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.

comment:3 Changed 23 months ago by Tim Graham

Summary: JSONField lookup and F ObjectsAllow querying JSONField with F objects
Triage Stage: UnreviewedAccepted

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.

comment:4 Changed 22 months ago by Mani

Owner: set to Mani
Status: newassigned

comment:5 Changed 22 months ago by 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 =
        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()))

comment:6 Changed 21 months ago by Srinivas Reddy Thatiparthy

Cc: Srinivas Reddy Thatiparthy added

comment:7 Changed 15 months ago by 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.

comment:8 Changed 15 months ago by Uxío

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

'SELECT ("myapp_myevent"."arguments" -> \'to\') FROM "myapp_myevent"'

If I use F() object:

'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;

Note: See TracTickets for help on using tickets.
Back to Top