Opened 10 months ago

Closed 4 months ago

#31991 closed Bug (wontfix)

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

Reported by: horpto Owned by: nobody
Component: Database layer (models, ORM) Version: 3.1
Severity: Normal Keywords: JSONfield
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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": []}'

Change History (3)

comment:1 Changed 10 months ago by Mariusz Felisiak

Resolution: wontfix
Status: newclosed
Summary: .raw method returns string instead of dictQuerySet.raw() method returns string instead of dict for JSONField().

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.

comment:2 Changed 4 months ago by Петр Eлагин

Resolution: wontfix
Status: closednew

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

comment:3 Changed 4 months ago by Mariusz Felisiak

Resolution: wontfix
Status: newclosed

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

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