QuerySet.raw() method returns string instead of dict for JSONField().

[horpto]

Imagine 2 tables:

class AtsPhoneCall(models.Model):
    created = models.DateTimeField(auto_now_add=True, db_index=True)

    class Meta:
        db_table = 'atscall'

class PhoneCall(models.Model):
    ats_call = models.OneToOneField(
        'phones.AtsPhoneCall',
        on_delete=models.PROTECT,
        related_name='call',
    )
    ivr_data = models.JSONField(null=True)

And raw query with join will give us wrong value of ivr_data. Old django.contrib.postgres.fields.JSONField returned dict value as new django.db.models.JSONField returns str instead of dict. Example from django shell:

>>> sql = '''
    join phones_phonecall as p on atscall.id = p.ats_call_id
    where ivr_data is not null
    limit 10
'''
...     select atscall.id, p.ivr_data
...     from atscall
...     join phones_phonecall as p on atscall.id = p.ats_call_id
...     where ivr_data is not null
...     limit 10
... '''
>>>
>>> AtsPhoneCall.objects.raw(sql)[0].ivr_data
DEBUG;(0.002)
    select atscall.id, p.ivr_data
    from atscall
    join phones_phonecall as p on atscall.id = p.ats_call_id
    where ivr_data is not null
    limit 10
; args=()
'{"v1": []}'
>>>
>>> for i in AtsPhoneCall.objects.raw(sql):
...     print(i.id, i.ivr_data, type(i.ivr_data))
...     break
...
DEBUG;(0.002)
    select atscall.id, p.ivr_data
    from atscall
    join phones_phonecall as p on atscall.id = p.ats_call_id
    where ivr_data is not null
    limit 10
; args=()
180621245012669 {"v1": []} <class 'str'>
>>> next(AtsPhoneCall.objects.raw(sql).iterator()).ivr_data
DEBUG;(0.008)
    select atscall.id, p.ivr_data
    from atscall
    join phones_phonecall as p on atscall.id = p.ats_call_id
    where ivr_data is not null
    limit 10
; args=()
'{"v1": []}'

[Mariusz Felisiak]

Thanks for the report. This behavior was changed in 0be51d2226fce030ac9ca840535a524f41e9832c as a part of fix for custom decoders. Unfortunately we have to skip the default psycopg2's behavior for all JSONField() (with psycopg2.extras.register_default_jsonb()) we cannot change this per a specific field. Moreover the new behavior is consistent for all database backends. You can avoid using raw() with Subquery():

AtsPhoneCall.objects.annotate(
    ivr_data=Subquery(PhoneCall.objects.filter(ats_call__pk=OuterRef('pk')).values('ivr_data')[:1]),
)

or call json.loads() on fetched data.

I will add a note to Django 3.1.1 release notes about this behavior change.

[Петр Eлагин]

i not use raw() with Subquery() ! i use direct query in database, pleasy make in settings use or not use register_default_jsonb\

[Mariusz Felisiak]

Петр, please don't shout and don't reopen closed tickets. If you don't use raw(), you can provide more detail in #32474.

[Ruben Nielsen]

Hello,

Chiming in here, as this is something that breaks a lot of functionality in our app when loading. I'm seeing this particular piece of the code as my problem:

        # Register dummy loads() to avoid a round trip from psycopg2's decode
        # to json.dumps() to json.loads(), when using a custom decoder in
        # JSONField.
        psycopg2.extras.register_default_jsonb(conn_or_curs=connection, loads=lambda x: x)

(django.db.backends.postgresql.base.py::DatabaseWrapper.get_new_connection)

I sort-of understand the reasoning behind this change, but I'm not sure how to get around it, if I just want the original functionality for my project, which only uses Postgres anyway. The comment says "when using a custom decoder", so shouldn't you check for this? If I use one, then fine, but if I don't then stop breaking psycopg2 for me... Or could this be controlled from settings.py? I know there are tonnes of settings already, but it would certainly help.

Or another path. How do I set a custom decoder for all my JSONFields? Earlier, everything was simply json.loads by default, so that was all good. I know how to make a custom field with a custom decoder, sure, but how would I then tie that into queryset.raw()?