Null value referenced from a JSONField using an expression that defines an output_field returns the string "null" instead of a None value

[Daniel Schaffer]

Example of breaking code:

class ThisHasAJSONField(Model):
    data = JSONField(null=True, blank=True, default=dict)

ThisHasAJSONField.objects.create(data=dict(test=None))

qs = ThisHasAJSONField.objects.annotate(
            test=Case(
                When(id__isnull=True, then=F("data__test")),
                default=F("data__test"),
                output_field=IntegerField(null=True),
            )
        )

When referencing a null value using a JSON lookup from an expression that defines an output_field, the null value is converted to the string "null" instead of being treated as a None value. If the output_field is one that requires some sort of value conversion like IntegerField, it will raise a ValueError

See repro repo: ​https://github.com/DanielSchaffer/django_json_null_expression_repro

[Simon Charette]

Setting Case(output_field) doesn't issue a CAST.

​Using a proper Cast should address your issue.

Cast(Case(
    When(
        id__isnull=True,
        then=F("data__test")
     ),
     default=F("data__test"),
), IntegerField(null=True))

Please TicketClosingReasons/UseSupportChannels before filling a bug report the next time.

[Daniel Schaffer]

Replying to Simon Charette:

Setting Case(output_field) doesn't issue a CAST.

​Using a proper Cast should address your issue.

That's a better workaround, but it's still a workaround isn't it? I'd argue this is a bug for two reasons:

• I wouldn't ever expect an ORM to implicitly convert a null value to the string "null"
• it works differently depending on what the JSON structure looks like: if the key doesn't exist at all, everything works as expected, but it breaks if there's an actual defined value of null

[Simon Charette]

I wouldn't ever expect an ORM to implicitly convert a null value to the string "null"

It doesn't convert it, that's the thing; it's just how the database adapter you are using is returning it straight from the database in its encoded JSON form.

it works differently depending on what the JSON structure looks like: if the key doesn't exist at all, everything works as expected, but it breaks if there's an actual defined value of null

I would argue that it's an eventuality you should build your application to be resilient against. Without a schema attached to your JSONField (something that Django doesn't support) there is no way for the ORM to know that you've strictly stashed nullable integers in your test key.

Without an explicit output_field the inferred one should be JSONField which ​has a method that would have unserialized the string properly (at least on Django 4.2+). It's the very misuse of output_field that appears to the bug here.