Opened 6 years ago
Closed 4 years ago
#29769 closed New feature (duplicate)
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 )
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.
Change History (10)
follow-up: 2 comment:1 by , 6 years ago
Description: | modified (diff) |
---|
comment:2 by , 6 years ago
comment:3 by , 6 years ago
Summary: | JSONField lookup and F Objects → Allow querying JSONField with F objects |
---|---|
Triage Stage: | Unreviewed → Accepted |
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 by , 6 years ago
Owner: | set to |
---|---|
Status: | new → assigned |
comment:5 by , 6 years ago
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()))
comment:6 by , 6 years ago
Cc: | added |
---|
comment:7 by , 6 years ago
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 by , 6 years ago
@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;
comment:10 by , 4 years ago
Resolution: | → duplicate |
---|---|
Status: | assigned → closed |
Replying to g4borg:
Note that I do have found that the workaround with
RawSQL('jsonfield->lookup->>data')
works as replacement for theF()
call, in my specific use case, but it results in a suboptimal solution imho.