Unable to do key lookup when a key is a number on PostgreSQL JSONB field

[Mikail]

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

[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...)

[Mikail]

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?