Opened 4 years ago

Closed 4 years ago

#32483 closed Bug (fixed)

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

Reported by: Matthew Cornell Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: 3.1
Severity: Normal Keywords: jsonfield querying sqlite
Cc: sage Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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'

Change History (14)

comment:1 by Rohith P R, 4 years ago

Triage Stage: UnreviewedAccepted

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)]>

comment:2 by Matthew Cornell, 4 years ago

Description: modified (diff)

comment:3 by Mariusz Felisiak, 4 years ago

Cc: sage added
Severity: NormalRelease blocker
Summary: querying JSONField in sqlite returns ints for booleansQuerySet.values()/values_list() with JSONField returns integers instead of booleans on SQLite.

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.

comment:4 by Mariusz Felisiak, 4 years ago

Component: Database layer (models, ORM)Documentation
Severity: Release blockerNormal

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.

comment:5 by Mariusz Felisiak, 4 years ago

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.

comment:6 by Matthew Cornell, 4 years ago

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.

comment:7 by Mariusz Felisiak, 4 years ago

Component: DocumentationDatabase layer (models, ORM)
Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

comment:8 by GitHub <noreply@…>, 4 years ago

In c6b0762:

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

comment:9 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

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

comment:10 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

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

comment:11 by Mariusz Felisiak, 4 years ago

Has patch: set

comment:12 by Carlton Gibson, 4 years ago

Triage Stage: AcceptedReady for checkin

comment:13 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

In c4df8b86:

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

comment:14 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

Resolution: fixed
Status: assignedclosed

In 71ec102b:

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

Thanks Matthew Cornell for the report.

Note: See TracTickets for help on using tickets.
Back to Top