Error with values with JSON path lookup in Sqlite when value is numeric.

[Gordon Wrigley]

This Django 3.1.3, I only see this with Sqlite, it works fine with MySQL and Postgres.

When I do a path lookup with values like Bob.objects.values("my_json_field__position") if there is an integer, float or bool in "position" then I get a JSON decode error.
Strings, nones, dicts and lists all work, fetching the top level dict works and filtering on the path lookup works.

TypeError: the JSON object must be str, bytes or bytearray, not float

..\..\.venv\data_browser\lib\site-packages\django\db\models\query.py:287: in __iter__
    self._fetch_all()
..\..\.venv\data_browser\lib\site-packages\django\db\models\query.py:1308: in _fetch_all
    self._result_cache = list(self._iterable_class(self))
..\..\.venv\data_browser\lib\site-packages\django\db\models\query.py:111: in __iter__
    for row in compiler.results_iter(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size):
..\..\.venv\data_browser\lib\site-packages\django\db\models\sql\compiler.py:1100: in apply_converters
    value = converter(value, expression, connection)
..\..\.venv\data_browser\lib\site-packages\django\db\models\fields\json.py:79: in from_db_value
    return json.loads(value, cls=self.decoder)

[Sage Abdullah]

This is because SQLite's JSON_EXTRACT returns deserialized values. A simple solution is just to add TypeError ​here, which will also partially "solve" problems with people who use the JSON data type on PostgreSQL. Another option is to wrap the value in JSON_QUOTE... which I think might be the better option.

I'm guessing there's also a bug in querying __key on {"key": "\"value\""}, which will return value rather than "value". I think wrapping the value in JSON_QUOTE should fix this as well... but it would break ordering (and maybe some other stuff).

[Sage Abdullah]

​PR. I opted to catch TypeError instead. Need opinions on how to proceed.

[Gordon Wrigley]

I should add that I see this on both Windows and Linux

[Mariusz Felisiak <felisiak.mariusz@…>]

In 7408c4cd:

Refs #32203 -- Added tests for QuerySet.values()/values_list() on key transforms with non-trivial values.

[Mariusz Felisiak <felisiak.mariusz@…>]

In fe6e5824:

Fixed #32203 -- Fixed QuerySet.values()/values_list() crash on key transforms with non-string values on SQLite.

Thanks Gordon Wrigley for the report.

[Mariusz Felisiak <felisiak.mariusz@…>]

In a2abeb3:

[3.1.x] Refs #32203 -- Added tests for QuerySet.values()/values_list() on key transforms with non-trivial values.

Backport of 7408c4cd157ae18971302a2baf7aacce7c204a09 from master

[Mariusz Felisiak <felisiak.mariusz@…>]

In a7935fe:

[3.1.x] Fixed #32203 -- Fixed QuerySet.values()/values_list() crash on key transforms with non-string values on SQLite.

Thanks Gordon Wrigley for the report.

Backport of fe6e5824218bab7cf47dee112d68325b338f9947 from master