Opened 6 months ago

Last modified 4 days ago

#28291 assigned Bug

ArrayField cannot contain JSONField; causes SQL error

Reported by: Richard Eames Owned by: Zac Yauney
Component: contrib.postgres Version: master
Severity: Normal Keywords:
Cc: David Hagen Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


Using JSONField as the subfield of ArrayField causes an error with casting:

psycopg2.ProgrammingError: column "field" is of type jsonb[] but expression is of type text[]
LINE 1: ..."postgres_tests_jsonarraymodel" ("field") VALUES (ARRAY['{"a...


class JSONArrayModel(PostgreSQLModel):
    field = ArrayField(JSONField(default=dict))

instance = JSONArrayModel(field=[{'a': 1 }, {'b': 2}])

Change History (4)

comment:1 Changed 6 months ago by Tim Graham

Triage Stage: UnreviewedAccepted

PR with a failing test.

comment:2 Changed 6 months ago by Zac Yauney

Owner: set to Zac Yauney
Status: newassigned

comment:3 Changed 8 weeks ago by Zac Yauney

Owner: changed from Zac Yauney to Zac Yauney

Using different GitHub Profile

comment:4 Changed 4 days ago by David Hagen

Cc: David Hagen added

I did some investigation to figure out why this happens because it is kind of annoying to be unable to reuse Fields backed by JsonField in ArrayFields.

The problem comes in the call to psycopg2.extensions.cursor.execute(sql, params) in django.db.backends.utils.CursorWrapper._execute (line 85 in Django 2.0). In this example, execute is called with sql = ... VALUES (%s) ... and params = [[JsonAdaptor({"a": 1}), JsonAdaptor({"a": 1})]], where JsonAdaptor is django.contrib.postgres.fields.JsonAdaptor which is a thin wrapper around psycopg2._json.Json. Now, execute is C code in psycopg2 which expands the SQL command to ... VALUES (ARRAY['{"a": 1}', '{"b": 2}']). It gets those string representations of the JSON by calling Json.getquoted. If the field has type jsonb then the quoted string appears to be a perfectly valid value to insert into the table. However, if the field has type jsonb[], then an array of quoted strings does not appear to be a valid value to insert, which is why the error is raised. The solution is to either cast the strings to jsonb or to cast the whole array to jsonb[] like this:

... VALUES (ARRAY['{"a": 1}'::jsonb, '{"b": 2}'::jsonb]) ...
... VALUES (ARRAY['{"a": 1}', '{"b": 2}']::jsonb[]) ...

Whether the bug fundamentally lies with psycopg2 or django depends on whose duty it is to make sure the conversion to string properly casts to the right type, and I don't know that.

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