[Django] #31991: .raw method returns string instead of dict
Django
-------------------------------------+-------------------------------------
Reporter: horpto | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.1
layer (models, ORM) |
Severity: Normal | Keywords: JSONfield
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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": []}'
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31991>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: horpto | Owner: nobody
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: JSONfield | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => wontfix
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/31991#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: horpto | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: wontfix =>
Comment:
i not use raw() with Subquery() ! i use direct query in database, pleasy
make in settings use or not use register_default_jsonb\
--
Ticket URL: <https://code.djangoproject.com/ticket/31991#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: horpto | Owner: nobody
Component: Database layer | Version: 3.1
(models, ORM) |
Keywords: JSONfield | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => wontfix
Comment:
Петр, please don't shout and don't reopen closed tickets. If you don't use
`raw()`, you can provide more detail in #32474.
--
Ticket URL: <https://code.djangoproject.com/ticket/31991#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: horpto | Owner: nobody
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: JSONfield | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by 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()`?
--
Ticket URL: <https://code.djangoproject.com/ticket/31991#comment:4>