Query JSONField for with keys of numerals only

Question

I've taken the following example from Django's documentation, except replaced added a key '99':

>>> Dog.objects.create(name='Rufus', data={
... 'breed': 'labrador',
...     'owner': {
...         'name': 'Bob',
...         'other_pets': [{
...             'name': 'Fishy',
...         }],
...     },
... })
>>> Dog.objects.create(name='Meg', data={'breed': 'collie', '99': 'FINDME',})

>>> Dog.objects.filter(data__breed='collie')
<QuerySet [<Dog: Meg>]>

I want the following to also return the "Meg" Dog:

Dog.objects.filter(data__99='FINDME')

However, it seems that because my key is an integer, Django doesn't handle this properly. How do I have integer keys that are strings in python jsonfields?

score 5 · Answer 1 · edited Jun 20 '20 at 09:12

There is a wontfix Django ticket about this.

It is documented behavior:

If the key is an integer, it will be interpreted as an index lookup in an array.

If the key you wish to query by clashes with the name of another lookup, use the jsonfield.contains lookup instead.

So the suggested solution is to use jsonfield.contains:

Dog.objects.filter(data__contains={'99': 'FINDME'})

Anyway I suggest you to avoid numeric keys inside json fields because with the contains approach you cannot perform startswith and other similar lookups (see this similar question) such as Dog.objects.filter(data__99__startswith='FIND').

score 1 · Accepted Answer · answered May 10 '17 at 22:10

1

It doesn't look like there's a good option. Here's a snippet from django/contrib/postgres/fields/jsonb.py:

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".format(lhs), [key_transforms] + params
    try:
        int(self.key_name)
    except ValueError:
        lookup = "'%s'" % self.key_name
    else:
        lookup = "%s" % self.key_name
    return "%s -> %s" % (lhs, lookup), params

From this, it looks like it tries to turn every key into an integer, and use that as the key if possible.

Here's a hack that you could do to make the query the way you want it:

def jsonb_integer_keys_to_str(qs, key):
  return qs.model.objects.raw(qs.query.__str__().replace('-> {}'.format(key), "-> '{}'".format(key)))

And use it as such:

jsonb_integer_keys_to_str(Dog.objects.filter(data__99='FINDME'), 99)

This solution is overly specific, but will work in this case. What it's doing is modifying the postgres sql and putting quotes in the right place.

answered May 10 '17 at 22:10

theicfire

2,719
2
26
29

Why don't you just store key as an integer as well? instead of `'99': 'FINDME'` use `99: 'FINDME'` i.e. `data={'breed': 'collie', 99: 'FINDME',}` – Aamir Rind May 10 '17 at 22:22
1

Great question! Turns out django turns all json keys into strings :(. I'm not sure why. – theicfire May 10 '17 at 22:44
2

Oh I know why -- because JSON doesn't allow integer keys – theicfire May 10 '17 at 22:47
Right, What is your Django version? – Aamir Rind May 10 '17 at 22:58
For future readers and for you @theicfire, this will work if you just want to access the (key, value), the internal functionality of integer key is to be `interpreted as an index lookup in an array` (more [here](https://www.postgresql.org/docs/9.3/static/functions-json.html#FUNCTIONS-JSON-OP-TABLE)) hence with above hack you won't be able to perform queries such as `data__owner__other_pets__0__name`. So better avoid integer keys. – Aamir Rind May 10 '17 at 23:57
I was looking at 1.10, but 1.11 also has the same issue. I think that statement you're pointing to isn't always correct, for example, in my `data__99` query, django is smart enough to know I'm not trying to look at the 99th element of a JsonField (it's not an array) – theicfire May 11 '17 at 00:29

Query JSONField for with keys of numerals only

2 Answers2