Opened 4 years ago

Closed 4 years ago

Last modified 3 years ago

#32226 closed Bug (fixed)

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

Reported by: Adam Johnson Owned by: Wu Haotian
Component: Database layer (models, ORM) Version: 3.0
Severity: Normal Keywords:
Cc: Tom Forbes, Michael Christofides Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no
Pull Requests:15611 merged, 14574 merged, 14062 unmerged, 14061 unmerged, 13867 unmerged, 13858 unmerged, 13859 unmerged, 13855 unmerged, 13861 unmerged, 13862 unmerged, 13860 unmerged, 13865 unmerged, 13866 unmerged, 13863 unmerged

Description

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().

Change History (20)

comment:1 by Carlton Gibson, 4 years ago

Triage Stage: UnreviewedAccepted

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

comment:2 by kosc, 4 years ago

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

comment:3 by kosc, 4 years ago

Owner: changed from nobody to kosc
Status: newassigned

comment:4 by Adam Johnson, 4 years ago

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

comment:5 by Tom Forbes, 4 years ago

Cc: Tom Forbes added

comment:6 by Tom Forbes, 4 years ago

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.

comment:7 by Adam Johnson, 4 years ago

It could be something to do with JSONField changes?

in reply to:  6 comment:8 by kosc, 4 years ago

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')).

Last edited 4 years ago by kosc (previous) (diff)

in reply to:  7 comment:9 by Mariusz Felisiak, 4 years ago

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.

comment:10 by ankitdawadi, 4 years ago

Owner: changed from kosc to ankitdawadi

comment:11 by udaykiran, 4 years ago

Owner: changed from ankitdawadi to udaykiran

we have to add a condition for json in explain_query_prefix

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

comment:12 by Jacob Walls, 4 years ago

Has patch: set

comment:13 by Adam Johnson, 4 years ago

Patch needs improvement: set

comment:14 by Michael Christofides, 4 years ago

Cc: Michael Christofides added

comment:15 by Mariusz Felisiak, 4 years ago

Owner: udaykiran removed
Status: assignednew

comment:16 by Wu Haotian, 4 years ago

Has patch: unset
Owner: set to Wu Haotian
Patch needs improvement: unset
Status: newassigned

comment:18 by Tim McCurrach, 4 years ago

Triage Stage: AcceptedReady for checkin

comment:19 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

Resolution: fixed
Status: assignedclosed

In aba9c2de:

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

comment:20 by GitHub <noreply@…>, 3 years ago

In f4f2afeb:

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

Follow up to aba9c2de669dcc0278c7ffde7981be91801be00b.

Note: See TracTickets for help on using tickets.
Back to Top