[Django] #32981: raw sql with postgres jsonb_array_elements no longer deserialises in Django 3.2.5, worked in 2.2.*

Django

unread,

Aug 1, 2021, 6:08:36 PM8/1/21

to django-...@googlegroups.com

#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.

Django

unread,

Aug 2, 2021, 12:08:39 AM8/2/21

to django-...@googlegroups.com

#32981: raw sql with postgres jsonb_array_elements no longer deserialises in Django
3.2.5, worked in 2.2.*

-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):

* status: new => closed
* resolution: => duplicate
* component: Uncategorized => Database layer (models, ORM)
* type: Uncategorized => Bug

Comment:

Duplicate of #31991, see
[https://code.djangoproject.com/ticket/31991#comment:1 comment] and
[https://docs.djangoproject.com/en/stable/releases/3.1.1/ 3.1.1] release
notes.

--
Ticket URL: <https://code.djangoproject.com/ticket/32981#comment:1>

Django

unread,

Aug 2, 2021, 12:09:28 AM8/2/21

to django-...@googlegroups.com

#32981: raw sql with postgres jsonb_array_elements no longer deserialises in Django

3.2.5.