raw sql with postgres jsonb_array_elements no longer deserialises in Django 3.2.5.

[Tim Richardson]

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.

[Mariusz Felisiak]

Duplicate of #31991, see comment and ​3.1.1 release notes.