bulk_update None on JSON field sets the value as "null" string in Postgresql

[Amit Maniar]

Updating JSON field of Postgresql table to None stores "null" as a string when using bulk_update.

Model in models.py

# Create model in DB
class TestModel(models.Model):

    created_at = models.DateTimeField(null=False)
    label = models.CharField(max_length=64)
    json_row = models.JSONField(null=False)

    def __str__(self) -> str:
        return self.label

from django.utils import timezone

# Create few records in database
TestModel.objects.create(created_at=timezone.now(), label='test label 1', json_row={'key1': 'value1', 'key2': 1234})
TestModel.objects.create(created_at=timezone.now(), label='test label 2', json_row={'key1': 'value1', 'key2': 5678})

# Create a list to update the records
test_list = []
test_list.append(TestModel.objects.get(id=1))

# update JSON field to None
test_list[0].json_row = None

# Bulk update objects in fields.
TestModel.objects.bulk_update(test_list, fields=['json_row'])

# Check DB records, json_row of TestModel (id=1) will be "null" (null as a string value)

[Clifford Gama]

Hello, Amit. Thanks for taking the time to create this ticket.

I can't reproduce with:

tests/queries/models.py

@@ -789 +789 @@
 
     class Meta:
         required_db_features = {"supports_json_field"}
+
+
+class NonNullableJSONModel(models.Model):
+    json_field = models.JSONField(null=False)
+
+    class Meta:
+        required_db_features = {"supports_json_field"}

tests/queries/test_bulk_update.py

@@ -26 +26 @@
     SpecialCategory,
     Tag,
     Valid,
+    NonNullableJSONModel
 )
 
 
@@ -300 +301 @@
             JSONFieldNullable.objects.filter(json_field__has_key="c"), objs
         )
 
+    @skipUnlessDBFeature("supports_json_field")
+    @override_settings(DEBUG=True)
+    def test_non_nullable_json_field(self):
+        obj = NonNullableJSONModel.objects.create(json_field={"k": "val"})
+        obj.json_field = None
+        NonNullableJSONModel.objects.bulk_update([obj], ["json_field"])
+        self.assertIsNone(obj.json_field)
+        sql = connection.queries[-1]["sql"].lower()
+        self.assertIn("'null'::jsonb", sql)
+        self.assertNotIn("'\"null\"'::jsonb", sql)
+
     def test_nullable_fk_after_related_save(self):
         parent = RelatedObject.objects.create()
         child = SingleObject()

on PostgreSQL 16.8 and Django's main branch. I get a JSON null instead of a "null" string. Which is surprising since JSON null is supposed to be stored through Value(None, JSONField()) ​as documented here. I'd have expected this to use SQL NULL instead, raising IntegrityError, like update() and save().

[Natalia Bidart]

Hello Amit Maniar, thank you for this ticket. I think the description lacks some clarity: could you please provide concrete details, ideally a failing test, about what do you expect to find in the DB record for the example that you provided?

I believe that Clifford understood as that you may be reporting that "null" (with the double quotes) is being stored, but I think you meant that null as the string is stored (instead of an actual SQL NULL). I can replicate getting the json's null:

django-test=# select * from testapp_ticket36418model where json_row is NULL;
 id | label | json_row 
----+-------+----------
(0 rows)

django-test=# select * from testapp_ticket36418model where json_row = 'null';
 id |    label     | json_row 
----+--------------+----------
  1 | test label 1 | null
(1 row)

Clifford has kindly created #36419 to follow up on the second half of this.

[Amit Maniar]

Hi Natalia Bidart and Clifford, Apologies for the confusion. I was reporting that null as the string is stored instead of an actual SQL NULL. I used double quotes around null to specify null as a string value.

Thanks for creating #36419.

Replying to Natalia Bidart:

Hello Amit Maniar, thank you for this ticket. I think the description lacks some clarity: could you please provide concrete details, ideally a failing test, about what do you expect to find in the DB record for the example that you provided?

I believe that Clifford understood as that you may be reporting that "null" (with the double quotes) is being stored, but I think you meant that null as the string is stored (instead of an actual SQL NULL). I can replicate getting the json's null:

django-test=# select * from testapp_ticket36418model where json_row is NULL;
 id | label | json_row 
----+-------+----------
(0 rows)

django-test=# select * from testapp_ticket36418model where json_row = 'null';
 id |    label     | json_row 
----+--------------+----------
  1 | test label 1 | null
(1 row)

Clifford has kindly created #36419 to follow up on the second half of this.

[Clifford Gama]

Replying to Amit Maniar:

Thanks for the follow-up, Amit.

I was reporting that null as the string is stored instead of an actual SQL NULL. I used double quotes around null to specify null as a string value.

Ah, I see. Please note that an SQL NULL wouldn't have been possible in your example as null=False is set (unless you were expecting an IntegrityError to be raised), which is why it was a little confusing. The null you are seeing in the database is JSON scalar null, which is an explicit "empty value" marker in JSON syntax ('null'::jsonb). The string (which I had misunderstood you meant) would be '"null"'::jsonb.

Fixing #36419 should make bulk updating json_row=None store an SQL NULL instead of JSON null, which would raise an IntegrityError in your example.