Opened 7 years ago

Closed 6 years ago

Last modified 5 years ago

#28291 closed Bug (fixed)

ArrayField cannot contain JSONField; causes SQL error

Reported by: Richard Eames Owned by: vinay karanam
Component: contrib.postgres Version: dev
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 (9)

comment:1 by Tim Graham, 7 years ago

Triage Stage: UnreviewedAccepted

PR with a failing test.

comment:2 by Zac Yauney, 7 years ago

Owner: set to Zac Yauney
Status: newassigned

comment:3 by Zac Yauney, 7 years ago

Owner: changed from Zac Yauney to Zac Yauney

Using different GitHub Profile

comment:4 by David Hagen, 7 years ago

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.

in reply to:  1 comment:5 by Jim Biggs, 6 years ago

Replying to Tim Graham:

PR with a failing test.

I do not agree this ticket #29096 is a duplicate of #28291(ArrayField cannot contain JSONField; causes SQL error). I'm using PostgreSQL, and it does not result in an error for me. It works as you can clearly see from the output I posted.

My question is why the JSON object is being stringified?

Thanks... Jim

comment:6 by Tim Graham, 6 years ago

The test in the PR still fails with the error described here so I'm not sure why your code is working. Are you using JSONField from contrib.postgres or a third-party package?

Anyway, it's clear that JSONField with ArrayField doesn't work for one reason or another so I think we only need one ticket for that. If you find a solution for your problem that doesn't address this ticket, feel free to reopen the other one.

comment:7 by vinay karanam, 6 years ago

Owner: changed from Zac Yauney to vinay karanam

I've raised a new PR using the solution provided here.

comment:8 by Tim Graham <timograham@…>, 6 years ago

Resolution: fixed
Status: assignedclosed

In 3af695e:

Fixed #28291, #24726 -- Fixed ArrayField with JSONField and RangeFields.

comment:9 by Artem.Bernatskyy, 5 years ago

This was included in Django version 2.2a1.

pip install Django==2.2a1

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