[Django] #28291: ArrayField cannot contain JSONField; causes SQL error
Django
--------------------------------------------+------------------------
Reporter: Richard Eames | Owner: (none)
Type: Bug | Status: new
Component: contrib.postgres | Version: master
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
--------------------------------------------+------------------------
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:
{{{
#!python
class JSONArrayModel(PostgreSQLModel):
field = ArrayField(JSONField(default=dict))
instance = JSONArrayModel(field=[{'a': 1 }, {'b': 2}])
instance.save()
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28291>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: Richard Eames | Owner: (none)
Type: Bug | Status: new
Component: contrib.postgres | Version: master
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* stage: Unreviewed => Accepted
Comment:
[https://github.com/django/django/pull/8619 PR] with a failing test.
--
Ticket URL: <https://code.djangoproject.com/ticket/28291#comment:1>
Django
Reporter: Richard Eames | Owner: Zac Yauney
Type: Bug | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Zac Yauney):
* status: new => assigned
* owner: (none) => Zac Yauney
--
Ticket URL: <https://code.djangoproject.com/ticket/28291#comment:2>
Django
Reporter: Richard Eames | Owner: Zac Yauney
Type: Bug | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Zac Yauney):
* owner: Zac Yauney => Zac Yauney
Comment:
Using different GitHub Profile
--
Ticket URL: <https://code.djangoproject.com/ticket/28291#comment:3>
Django
Reporter: Richard Eames | Owner: Zac Yauney
Type: Bug | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by David Hagen):
* cc: David Hagen (added)
Comment:
I did some investigation to figure out why this happens because it is kind
of annoying to be unable to reuse `Field`s backed by `JsonField` in
`ArrayField`s.
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28291#comment:4>
Django
Reporter: Richard Eames | Owner: Zac Yauney
Type: Bug | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Jim Biggs):
Replying to [comment:1 Tim Graham]:
> [https://github.com/django/django/pull/8619 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
--
Ticket URL: <https://code.djangoproject.com/ticket/28291#comment:5>
Django
Reporter: Richard Eames | Owner: Zac Yauney
Type: Bug | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28291#comment:6>
Django
Reporter: Richard Eames | Owner: vinay
| karanam
Type: Bug | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by vinay karanam):
* owner: Zac Yauney => vinay karanam
Comment:
I've raised a new [https://github.com/django/django/pull/10222 PR] using
the solution provided
[https://github.com/psycopg/psycopg2/issues/585#issuecomment-327585721
here].
--
Ticket URL: <https://code.djangoproject.com/ticket/28291#comment:7>
Django
Reporter: Richard Eames | Owner: vinay
| karanam
Component: contrib.postgres | Version: master
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham <timograham@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"3af695eda24b874486ee8be7e0d729761b3bdc71" 3af695e]:
{{{
#!CommitTicketReference repository=""
revision="3af695eda24b874486ee8be7e0d729761b3bdc71"
Fixed #28291, #24726 -- Fixed ArrayField with JSONField and RangeFields.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28291#comment:8>
Django
Reporter: Richard Eames | Owner: vinay
| karanam
Type: Bug | Status: closed
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Artem.Bernatskyy):
This was included in Django version `2.2a1`.
`pip install Django==2.2a1`
--
Ticket URL: <https://code.djangoproject.com/ticket/28291#comment:9>