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.

[moshe nahmias]

It seems like the comments were done and test passes on the PR branch but fails on the main branch and is still relevant for 5.2 and the dev version

[Jacob Walls]

Friendly reminder to update the ticket flags when you're ready for another review.

[savanto]

Oh, ok, thanks! I added documentation and squashed into a single commit, so this is ready for final review.

[Jacob Walls]

Sounds good. Ready for checkin is only set by reviewers FYI, I was referring to the "needs..." flags, but no worries.

[savanto]

Thanks/sorry =)

[Sarah Boyce <42296566+sarahboyce@…>]

In a8716f3c:

Refs #36085 -- Moved compile_json_path to BaseDatabaseOperations.

[Sarah Boyce <42296566+sarahboyce@…>]

In 8620a3b0:

Fixed #36085 -- Added JSONField support for negative array indexing on SQLite.