ArrayField cannot contain JSONField; causes SQL error

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

Testcase:

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

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

[Tim Graham]

​PR with a failing test.

[Zac Yauney]

Using different GitHub Profile

[David Hagen]

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.

[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

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

[vinay karanam]

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

[Tim Graham <timograham@…>]

In 3af695e:

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

[Artem.Bernatskyy]

This was included in Django version 2.2a1.

pip install Django==2.2a1