QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL

[Adam Johnson]

Call .explain(format='json') on a QuerySet on PostgreSQL, and the result *looks* like JSON, but isn't:

In [5]: User.objects.filter(is_staff=True).explain(format='json')
Out[5]: "[{'Plan': {'Node Type': 'Seq Scan', 'Parallel Aware': False, 'Relation Name': 'auth_user', 'Alias': 'auth_user', 'Startup Cost': 0.0, 'Total Cost': 478.12, 'Plan Rows': 23, 'Plan Width': 157, 'Filter': 'is_staff'}}]"

One needs to use ast.literal_eval and json.dumps to get real JSON text that can be pasted into tools like ​PostgreSQL explain viewer:

In [10]: explain = User.objects.filter(is_staff=True).explain(format='json') ; print(json.dumps(ast.literal_eval(explain)))
[{"Plan": {"Node Type": "Seq Scan", "Parallel Aware": false, "Relation Name": "auth_user", "Alias": "auth_user", "Startup Cost": 0.0, "Total Cost": 478.12, "Plan Rows": 23, "Plan Width": 157, "Filter": "is_staff"}}]

I guess this is because psycopg2 loads the JSON as a Python list of dicts, then SQLCompiler.explain_query does yield ' '.join(str(c) for c in row), which turns that list into its repr().

[Carlton Gibson]

OK, thanks Adam, yes. EXPLAIN (FORMAT JSON) SELECT ... certainly gives you actual JSON. :)

[kosc]

Is this issue only for PostgreSQL? I see a similar result with at least MySQL.

[Adam Johnson]

I can imagine it exists there too, I only had PostgreSQL to hand.

[Tom Forbes]

I wonder if this changed at some point, because when I was developing this feature I’m almost certain that it returned a string type with valid JSON rather than a list of dicts 🤔. I can’t see anything in the psycopg2 release notes to support that though, so maybe I’m misremembering.

[Adam Johnson]

It could be something to do with JSONField changes?

[kosc]

Replying to Tom Forbes:

I wonder if this changed at some point, because when I was developing this feature I’m almost certain that it returned a string type with valid JSON rather than a list of dicts 🤔. I can’t see anything in the psycopg2 release notes to support that though, so maybe I’m misremembering.

Hello! I see that query "EXPLAIN (FORMAT JSON) SELECT * FROM table_name;" returns valid JSON strings in double-quotes, but Django returns a string with single-quotes, and when I trying to json.loads string from django I getting an error: "JSONDecodeError: Expecting property name enclosed in double quotes: line 1 column 3 (char 2)".
Also, when I trying to replace single quotes with double quotes, I getting another error: "JSONDecodeError: Expecting value: line 1 column 55 (char 54)".

Here is a string returning by Django ORM: "[{'Plan': {'Node Type': 'Seq Scan', 'Parallel Aware': False, 'Relation Name': 'main_testmodel', 'Alias': 'main_testmodel', 'Startup Cost': 0.0, 'Total Cost': 11.4, 'Plan Rows': 140, 'Plan Width': 520}}]" (the query: TestModel.objects.all().explain(format='json')).

[Mariusz Felisiak]

Replying to Adam (Chainz) Johnson:

It could be something to do with JSONField changes?

I can reproduce it at c1c163b42717ed5e051098ebf0e2f5c77810f20e, so it's not related with JSONField changes.

[udaykiran]

we have to add a condition for json in explain_query_prefix

if format == 'JSON': prefix = json.loads(prefix)

[Jacob Walls]

​PR

[Wu Haotian]

​https://github.com/django/django/pull/14574

[Mariusz Felisiak <felisiak.mariusz@…>]

In aba9c2de:

Fixed #32226 -- Fixed JSON format of QuerySet.explain() on PostgreSQL.

[GitHub <noreply@…>]

In f4f2afeb:

Refs #32226 -- Fixed JSON format of QuerySet.explain() on PostgreSQL when format is uppercased.

Follow up to aba9c2de669dcc0278c7ffde7981be91801be00b.