#30566: Unable to do key lookup when a key is a number on PostgreSQL JSONB field
-------------------------------------+-------------------------------------
Reporter: | Owner: (none)
NyanKiyoshi |
Type: Bug | Status: new
Component: | Version: 2.2
contrib.postgres | Keywords: json, jsonb,
Severity: Normal | postgres
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Hi,
Currently, if we try working for example with the following model using a
JSONField (jsonb):
{{{#!python
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:
{{{#!python
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:
{{{#!sql
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`:
{{{#!python
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:
{{{#!python
try:
int(self.key_name)
except ValueError:
lookup = "'%s'" % self.key_name
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30566>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/054.42dba494b8af1a2ec206ef0af3cb7216%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
