Django DB connection returns incorrect type for JSON objects with a top-level list

[Christophe Pettus]

Test case here: ​https://github.com/Xof/django_test_str

Back to at least Django 4.0, there is a bug in the database interface under
specific conditions:

1. PostgreSQL.
2. The cursor is created from the django.connection object.
3. The data type being returned is JSON.
4. The top-level structure in the JSON blog is a list, rather than an object.

In that situation, cursor.execute returns a str rather than a Python list.
This bug does not exist if the query is executed using psycopg2 directly.

This test case should be self-contained, although you do need a local PostgreSQL
database. The only dependency besides Django is psycopg2.

To run the test, use:

./manage.py test --keepdb

(--keepdb is not strictly required, but it avoids some unrelated errors.)

[Tim Graham]

It's an intentional behavior introduced in 0be51d2226fce030ac9ca840535a524f41e9832c (#31956). The relevant code:

        # Register dummy loads() to avoid a round trip from psycopg2's decode
        # to json.dumps() to json.loads(), when using a custom decoder in
        # JSONField.
        psycopg2.extras.register_default_jsonb(conn_or_curs=connection, loads=lambda x: x)

If you have an alternate proposal to address that issue and avoid this one, please reopen the ticket, however, I'm not sure there's much value in addressing psycopg2-specific issues at this point.

[Christophe Pettus]

I'm not sure there's much value in addressing psycopg2-specific issues at this point.

It's not psycopg2-specific; same thing happens on psycopg v3.

I don't have an immediate fix, but it's still a bug. I'd like to leave it open and assigned to me, and I'll come up with a proposed fix.

[Tim Graham]

You're correct that I was mistaken in claiming it's psycopg2-specific, however, ​the same design decision applies for psycopg:

        # Register a no-op dumper to avoid a round trip from psycopg version 3
        # decode to json.dumps() to json.loads(), when using a custom decoder
        # in JSONField.
        ctx.register_loader("jsonb", TextLoader)

[Christophe Pettus]

It looks like the problem is when a custom encoder is passed into the JSONField definition, and you want to just pass along the raw JSON blog text to it rather than accept psycopg(2)'s decoding?

[Tim Graham]

Correct. See discussion and duplicate reports: ticket:31956#comment:8, #31991, #32474.