QuerySet.values()/values_list() with JSONField returns integers instead of booleans on SQLite.

[Matthew Cornell]

I have a model with a JSONField:

class PredictionData(models.Model):
    data = models.JSONField()

One of the rows contains this dict: {'value': True}.

I'm querying the model's JSON using 'data__value':

PredictionData.objects.values_list('data', 'data__value')

I get correct results for postgres (a boolean) but incorrect for sqlite3 (an int). For this query, sqlite3 wrongly returns:

({'value': True}, 1)

whereas postgres correctly returns

({'value': True}, True)

Same behavior with False/0.

versions:
Python 3.9.1
sqlite3.sqlite_version # '3.33.0'
django.version # '3.1.7'

[Rohith P R]

I was able to reproduce the issue:

from django.db import models


class Messages(models.Model):
    json_field = models.JSONField()

>>> from app.models import Messages
>>> result = Messages.objects.values_list('json_field', 'json_field__is_true')
>>> print(result)
<QuerySet [({'is_true': True}, 1)]>

[Mariusz Felisiak]

Regression tests:

diff --git a/tests/model_fields/test_jsonfield.py b/tests/model_fields/test_jsonfield.py
index 89b78de708..43ca00a4d1 100644
--- a/tests/model_fields/test_jsonfield.py
+++ b/tests/model_fields/test_jsonfield.py
@@ -804,6 +804,16 @@ class TestQuerying(TestCase):
             with self.subTest(lookup=lookup):
                 self.assertEqual(qs.values_list(lookup, flat=True).get(), expected)
 
+    def test_key_values_boolean(self):
+        qs = NullableJSONModel.objects.filter(value__h=True)
+        tests = [
+            ('value__h', True),
+            ('value__i', False),
+        ]
+        for lookup, expected in tests:
+            with self.subTest(lookup=lookup):
+                self.assertIs(qs.values_list(lookup, flat=True).get(), expected)
+
     @skipUnlessDBFeature('supports_json_field_contains')
     def test_key_contains(self):
         self.assertIs(NullableJSONModel.objects.filter(value__foo__contains='ar').exists(), False)

Related to #27481 and #32203.

[Mariusz Felisiak]

It seems there is not much we can do, the SQL query is correct. Unfortunately JSON_EXTRACT() returns integers for boolean values and it's a ​documented behavior:

The json_extract(X,P1,P2,...) extracts and returns one or more values from the well-formed JSON at X. If only a single path P1 is provided, then the SQL datatype of the result is NULL for a JSON null, INTEGER or REAL for a JSON numeric value, an INTEGER zero for a JSON false value, an INTEGER one for a JSON true value, ...

There is no way to recognize that we should automatically cast a value to boolean, you can use Cast('json_field__is_true', models.BooleanField()).

Changing to a documentation issue.

[Mariusz Felisiak]

OK, there is a way with using JSON_TYPE(), however it's quite complicated (see the ​draft changeset) and model_fields.test_jsonfield.TestQuerying.test_isnull_key_or_none doesn't work with this change. I would prefer to document this caveat in Django < 4.0 and keep this ticket as a cleanup/optimization.

[Matthew Cornell]

Thank you for looking into this Rohith and Mariusz. For now I will avoid the query feature and just pull the data out once Django has deserialized the field.

[GitHub <noreply@…>]

In c6b0762:

Refs #32483 -- Doc'd caveat about using JSONField key transforms to booleans with QuerySet.values()/values_list() on SQLite.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 49970b5:

[3.2.x] Refs #32483 -- Doc'd caveat about using JSONField key transforms to booleans with QuerySet.values()/values_list() on SQLite.

Backport of c6b07627fcb5d1c8d2082714ef5adb63bee6cf4c from master

[Mariusz Felisiak <felisiak.mariusz@…>]

In 5ab1b7bc:

[3.1.x] Refs #32483 -- Doc'd caveat about using JSONField key transforms to booleans with QuerySet.values()/values_list() on SQLite.

Backport of c6b07627fcb5d1c8d2082714ef5adb63bee6cf4c from master

[Mariusz Felisiak]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In c4df8b86:

Refs #32483 -- Added tests QuerySet.values()/values_list() on key transforms with structures containing booleans.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 71ec102b:

Fixed #32483 -- Fixed QuerySet.values()/values_list() on JSONField key transforms with booleans on SQLite.

Thanks Matthew Cornell for the report.