[FIXED] After upgrade, raw sql queries return json fields as strings on postgres

Issue

I am upgrading a Django app from 2.2.7 to 3.1.3. The app uses Postgres 12 & psycopg2 2.8.6.

I followed the instructions and changed all my django.contrib.postgres.fields.JSONField references to django.db.models.JSONField, and made and ran the migrations. This produced no changes to my schema (which is good.)

However, when I execute a raw query the data for those jsonb columns is returned as text, or converted to text, at some point. I don't see this issue when querying the models directly using Model.objects.get(...).

import os, django
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "big_old_project.settings")
django.setup()

with connection.cursor() as c:
    c.execute("select name, data from tbl where name=%s", ("rex",))
    print(c.description)
    for row in c.fetchall():
        for col in row:
            print(f"{type(col)} => {col!r}")

(Column(name='name', type_code=1043), Column(name='data', type_code=3802))
<class 'str'> => 'rex'
<class 'str'> => '{"toy": "bone"}'

[edit] Using a raw connection gives the expected results:

conn = psycopg2.connect("dbname=db user=x password=z")
with conn.cursor() as c:
    ...
<class 'str'> => 'rex'
<class 'dict'> => {'toy': 'bone'}

Trying the old trick of "registering" the adapter doesn't work, and shouldn't be needed anyway.

import psycopg2.extras
psycopg2.extras.register_json(oid=3802, array_oid=3807, globally=True)

This app has a lot of history, so maybe something is stepping on psycopg2's toes? I can't find anything so far, and have commented out everything that seems tangentially related.

Going through the release notes didn't help. I do use other postgres fields so I can't delete all references to contrib.postgres.fields from my models.

Any ideas as to why this is happening would be greatly appreciated.

Solution

To add to @Andrew Backer's helpful answer, this is apparently intentional. From the 3.1.1 release notes:

Fixed a QuerySet.order_by() crash on PostgreSQL when ordering and grouping by JSONField with a custom decoder (#31956). As a consequence, fetching a JSONField with raw SQL now returns a string instead of pre-loaded data. You will need to explicitly call json.loads() in such cases.

It's surprising to find an API-incompatible change as an aside in a bugfix release. For now I'll be adding json.loads() calls since, as already mentioned, there's no guarantee the ::json workaround doesn't break as well!

Answered By - btmills