Opened 4 months ago

Closed 4 months ago

Last modified 4 months ago

#32981 closed Bug (duplicate)

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

Reported by: Tim Richardson Owned by: nobody
Component: Database layer (models, ORM) Version: 3.2
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (2)

comment:1 Changed 4 months ago by Mariusz Felisiak

Component: UncategorizedDatabase layer (models, ORM)
Resolution: duplicate
Status: newclosed
Type: UncategorizedBug

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

comment:2 Changed 4 months ago by Mariusz Felisiak

Summary: raw sql with postgres jsonb_array_elements no longer deserialises in Django 3.2.5, worked in 2.2.*raw sql with postgres jsonb_array_elements no longer deserialises in Django 3.2.5.
Note: See TracTickets for help on using tickets.
Back to Top