id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
32981	raw sql with postgres jsonb_array_elements no longer deserialises in Django 3.2.5.	Tim Richardson	nobody	"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. 



"	Bug	closed	Database layer (models, ORM)	3.2	Normal	duplicate			Unreviewed	0	0	0	0	0	0