SQLite backend raises exception on negative array indices in JSONField

[savanto]

Positive jsonfield array index queries can be constructed the usual way with kwargs (ie. Model.objects.filter(jsonfield__2=1)), while negative jsonfield array index query must be constructed using the splat operator on a dict: Model.objects.filter(**{"jsonfield__-2": 1})). When querying jsonfields in this way on the SQLite backend, the following error arises:

django.db.utils.OperationalError: bad JSON path: '$[-2]'

When constructing the query, the JSON path constructor simply appends any numerical value to the path (​https://github.com/django/django/blob/5.1.4/django/db/models/fields/json.py#L155). But the SQLite backend uses a special syntax for performing negative-indexing in jsonfields (​https://sqlite.org/json1.html#path_arguments) that is different from other backends: negative indices must be prepended by a literal # character.

[savanto]

​https://github.com/django/django/pull/19030

[hesham hatem]

Thanks savanto for your contributions!!

I hope you and tim graham and lay monage find my PR well done.
thanks again!!

​https://github.com/django/django/pull/19182

[Jacob Walls]

A ​PR was opened to start with a related refactor. It needs a little tweak.

[savanto]

Since ​https://github.com/django/django/pull/19182 was closed without merge a few weeks ago, I incorporated the refactor work from it back into ​https://github.com/django/django/pull/19030 and made the suggested changes from discussions from both PRs there.