#32981: raw sql with postgres jsonb_array_elements no longer deserialises in
Django
3.2.5, worked in 2.2.*
-----------------------------------------+------------------------
Reporter: timrichardson | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 3.2
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+------------------------
A query that returned a dicts in the fetchall() result now returns a
string which is json. In django 3.2.5, it is no longer deserialised to a
dict.
This is a very big change in behaviour. Did I miss something? I must have,
somewhere. I will keep looking. This was a nasty surprise :)
Seen on postgres 12, django 3.2.5 and both psycopg2-binary==2.8.6 \= and
latest sycopg2-binary
The database table has a jsonb field.
Here is a simplified value for the field jdata
{{{
{
"Fulfilments":[
"Pick":{
"Lines":[
{
"SKU":"sku1",
"Name":"furniture1",
},
{
"SKU":"sku2",
"Name":"furniture2",
},
]
},
]
}
}}}
Example:
{{{
from django.db import connection
def test_raw_sql():
dear_api = setup_dear(dear_entity=os.getenv("DEAR_ENTITY"))
dear_cached_api = DearCachedAPI(dear_api=dear_api)
sql = """
select jdata,
jsonb_array_elements(cached_dear_dearcache.jdata#>'{Fulfilments}')->'Pick'
as picks
from cached_dear_dearcache
"""
with connection.cursor() as cursor:
cursor.execute(sql)
rows = cursor.fetchall()
return rows
}}}
both the first and the second element of the results tuple should be a
dict.
In Django 2.2.x, this is what happens.
For example, the second element is a dict:
pick_lines = row[1]
then
pick_lines is {'Lines':[{"SKU": "sku1" ....
In Django 3.2.5 both elements per row are now str. It is a json string,
not deserialised into a dict. It is definitely json, as it uses null for
None for instance.
This is a big problem, as iterators expecting a dict no longer work,
obviously.
for e.g.
{{{
for line in pick_lines['Lines']:
....
}}}
now breaks when pick_lines is a (json) str.
Although to be honest, the easiest way to see this is that even the first
element, jsonb, is not deserialised.
This problem is reproducible simply by swapping to django==3.2.*
It works for every version of 2.2.*
This is based on production code running on postgresql from v 9.6 to v 12.
I have reproduced the 3.2.5 behaviour in postgresql v10 and v12.
--
Ticket URL: <https://code.djangoproject.com/ticket/32981>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/056.58b1d4b319ecc4aa25c31de5d28baacf%40djangoproject.com.
