Changes between Initial Version and Version 2 of Ticket #36494

Timestamp:

Oct 8, 2025, 6:29:46 AM (6 days ago)

Author:

Jacob Walls

Comment:

Simon suggested bracketing Subquery for the moment and just focusing on expression support generally.

With just F()...

    def test_lookups_using_expression(self):
        contains_lookups = {"contains", "contained_by"}
        lookups_not_matching = {"gt", "lt", "has_key"}
        text_lookups = {
            "has_key",
            "iexact",
            "icontains",
            "startswith",
            "istartswith",
            "endswith",
            "iendswith",
            "regex",
            "iregex",
        }
        for lookup in JSONField.get_lookups():
            with self.subTest(lookup=lookup):
                if lookup == "isnull":
                    continue  # not allowed, rhs must be a literal boolean.
                if (
                    lookup in contains_lookups
                    and not connection.features.supports_json_field_contains
                ):
                    continue
                if lookup in text_lookups:
                    rhs = KT("value__bar")
                else:
                    rhs = F("value__bar")
                qs = NullableJSONModel.objects.filter(**{f"value__bar__{lookup}": rhs})
                if lookup in lookups_not_matching:
                    self.assertIs(qs.exists(), False)
                else:
                    # might need tweaking
                    self.assertQuerySetEqual(qs, NullableJSONModel.objects.filter(value__has_key="bar"))

... we see three classes of failures:

1. gt/gte/lt/lte

  File "/Users/jwalls/django/django/db/models/fields/json.py", line 614, in process_rhs
    rhs_params = [json.loads(value) for value in rhs_params]
                  ~~~~~~~~~~^^^^^^^
  File "/Users/jwalls/cpython/Lib/json/__init__.py", line 346, in loads
    return _default_decoder.decode(s)
           ~~~~~~~~~~~~~~~~~~~~~~~^^^
  File "/Users/jwalls/cpython/Lib/json/decoder.py", line 345, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
               ~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jwalls/cpython/Lib/json/decoder.py", line 363, in raw_decode
    raise JSONDecodeError("Expecting value", s, err.value) from None
json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)

2. range

   django.db.utils.OperationalError: near "AND": syntax error
   

3. has_keys/has_any_keys

     File "/Users/jwalls/django/django/db/models/fields/json.py", line 278, in get_prep_lookup
       return [str(item) for item in self.rhs]
                                     ^^^^^^^^
   TypeError: 'KeyTransform' object is not iterable
   

---

I'm suggesting we close this ticket (in separate PRs) with a combination of adding support where possible (perhaps for range?), with failing at the python layer with more appropriate exceptions,

I think we should invite PRs per lookup to either raise a better exception (case 2 above is especially poor, sending malformed SQL) or add support if the effort is not massive. (At Django on the Med 2025 I made a small experiment with adding has_any_keys support on PostgreSQL: seemed slightly promising?)

All of the commits would probably be "Refs ...", and then I imagine closing this ticket manually once we decide the remaining edge cases are too difficult to implement.

Going through the new features features process *could* help ensure that "reviewer resources" are equitably competed for in this case, but I can't think of many other reasons to go through that process. I think in the absence of any glaring warning in the docs, my expectation is that all of JSONField's registered lookups work with expressions. I doubt we would add a release note.