#30566 closed Bug (wontfix)
Unable to do key lookup when a key is a number on PostgreSQL JSONB field
Reported by: | Mikail | Owned by: | |
---|---|---|---|
Component: | contrib.postgres | Version: | 2.2 |
Severity: | Normal | Keywords: | json, jsonb, postgres |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Hi,
Currently, if we try working for example with the following model using a JSONField (jsonb):
from django.contrib.postgres.fields import JSONField from django.db import models class MyProduct(models.Model): attributes = JSONField(default=dict, blank=True)
With some dummy JSON data and a lookup the keys:
json_data = {"123": ["82"], "hello": ["82"]} MyProduct.objects.create(attributes=json_data) MyProduct.objects.filter(attributes__123__has_key="82").first() # We get None, but we expect to match the product MyProduct.objects.filter(attributes__hello__has_key="82").first() # We get a match to the product
The two queries generated:
SELECT * FROM "product_myproduct" WHERE ("product_myproduct"."attributes" -> 123) SELECT * FROM "product_myproduct" WHERE ("product_myproduct"."attributes" -> 'hello')
So in the SQL queries we notice django passed the 123
lookup value as an integer instead of a string. Which, in PostgreSQL means that we want the JSONB value of the 123rd key–which is not what we want.
I would expect that the lookup actually sends everything as a string, no matter what. And instead, having to use another lookup to get a key by index, e.g. Q(field__key_at__1="something")
.
The same thing happens for the ->>
operator (which inherits the issue from ->
).
This issue happens at django.contrib.postgres.fields.jsonb.KeyTransform
:
class KeyTransform(Transform): operator = '->' nested_operator = '#>' def __init__(self, key_name, *args, **kwargs): super().__init__(*args, **kwargs) self.key_name = key_name def as_sql(self, compiler, connection): key_transforms = [self.key_name] previous = self.lhs while isinstance(previous, KeyTransform): key_transforms.insert(0, previous.key_name) previous = previous.lhs lhs, params = compiler.compile(previous) if len(key_transforms) > 1: return "(%s %s %%s)" % (lhs, self.nested_operator), [key_transforms] + params try: int(self.key_name) except ValueError: lookup = "'%s'" % self.key_name else: lookup = "%s" % self.key_name return "(%s %s %s)" % (lhs, self.operator, lookup), params
The snippet in question is the following, where we do the integer check:
try: int(self.key_name) except ValueError: lookup = "'%s'" % self.key_name
Change History (2)
follow-up: 2 comment:1 by , 5 years ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
comment:2 by , 5 years ago
Replying to Carlton Gibson:
This is the documented behaviour. See Key index and path lookups.
Any change would need to go via the DevelopersMailingList.
(Without really thinking about it, I'd guess a workaround might need to use a custom lookup...)
Thanks for the reply. Wouldn't it be useful for users to have a lookup allowing them to filter with a string key no matter what instead of having to implement it themselves?
This is the documented behaviour. See Key index and path lookups.
Any change would need to go via the DevelopersMailingList.
(Without really thinking about it, I'd guess a workaround might need to use a custom lookup...)