Opened 16 months ago

Closed 8 weeks ago

#28291 closed Bug (fixed)

ArrayField cannot contain JSONField; causes SQL error

Reported by: Richard Eames Owned by: vinay karanam
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

Description

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

Testcase:

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

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

Change History (8)

comment:1 Changed 16 months ago by Tim Graham

Triage Stage: UnreviewedAccepted

PR with a failing test.

comment:2 Changed 15 months ago by Zac Yauney

Owner: set to Zac Yauney
Status: newassigned

comment:3 Changed 11 months ago by Zac Yauney

Owner: changed from Zac Yauney to Zac Yauney

Using different GitHub Profile

comment:4 Changed 10 months 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.

comment:5 in reply to:  1 Changed 8 months ago by Jim Biggs

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 Changed 8 months ago by Tim Graham

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 Changed 2 months ago by vinay karanam

Owner: changed from Zac Yauney to vinay karanam

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

comment:8 Changed 8 weeks ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

In 3af695e:

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

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