Document limitations of using int()able keys with JSONField

[Shaheed Haque]

The documentation on performing queries inside JSONField values ​https://docs.djangoproject.com/en/2.0/ref/contrib/postgres/fields/#key-index-and-path-lookups says:

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

>>> Dog.objects.filter(data__owner__other_pets__0__name='Fishy')

Note the specific mention of array. While this might be true, it is not the whole truth as applied to dict/object. For example, given a dict/object whose keys are strings (as always in JSON) but which look like integers:

  "employee": {
    "415": {
      "email": "Sherlock.Holmes@acme.co.uk",
      "mobile": "0700 1234567",

how is one supposed to select the "415" bit? It turns out that the same syntax as for the array case applies:

Foo.objects.filter(snapshot__employee__415__mobile='0700 1234567')

This was not at all obvious to me at least, especially as if the "415" is looked up as the final key in the query, the syntax becomes very different:

Foo.objects.filter(snapshot__employee__has_key='415')

Since I wasted quite a bit of time on this, I thought it might be useful to strengthen the documentation in this area to clarify how to lookup:

• In arrays and dict/objects
  • if the key is the final term in the query
  • if the key is not the final term in the query

[Carlton Gibson]

OK, I'm going to provisionally accept this. If you can put together a patch we can have a look and see if there's a clarification to be made.

I'm half-minded to say wontfix since JSON keys must always be strings, and whilst, like '415', they might look like integers, there's no real ambiguity.
But lets see what improvement you have in mind. :)

[Shaheed Haque]

I've created ​PR 10077. Please consider.

[Tim Graham]

Closing per discussion on PR.

[Jacob Walls]

I think there's enough ambiguity and lack of support across databases to be worth some documentation here. (Thanks Clifford for pointing me at this from #37297.)

On Postgres, string keys that are int()able can't be queried in the usual way unless more lookups are chained afterward, because when len(key_transforms) == 1, we assume any int()able key is an int.

Then, if you do chain more lookups afterward, it works perfectly fine, but only on Postgres, where the #> ARRAY[... syntax is flexible enough to match either keys or indices. Other databases require specific SQL to match either a key or an index.

from django.db import models

class Person(models.Model):
    metadata = models.JSONField()

def run():
    metadata = {
        "0": "foo",
        "zero": "foo",
    }
    instance = Person.objects.create(metadata=metadata)
    # assert Person.objects.filter(metadata__0="foo").exists()  # fails
    assert Person.objects.filter(metadata__zero="foo").exists()  # passes
    
    metadata = {
        "0": {
            "nested": "foo"
        }
    }
        
    instance = Person.objects.create(metadata=metadata)
    assert Person.objects.filter(metadata__0__nested="foo").exists()  # works on PG only

Given all that, I think we should clarify that int()able keys aren't always queryable?

[Abheet]

I'd like to work on this ticket. I'll submit a documentation patch clarifying that int()-able keys in JSONField are not always queryable.

[Clifford Gama]

Thanks Jacob for reopening this!

[Jacob Walls <jacobtylerwalls@…>]

In badbcf2:

Fixed #29504 -- Documented limitations of integer-like keys in JSONField.

[Jacob Walls <jacobtylerwalls@…>]

In e9f88e3:

Refs #29504 -- Used "object" when referring to JSON keys in query docs.

[Jacob Walls <jacobtylerwalls@…>]

In 7e8a8ea:

[6.1.x] Fixed #29504 -- Documented limitations of integer-like keys in JSONField.

Backport of badbcf20e1a2894bf557d9cf0707367da4abfe41 from main.