[Django] #32519: Add INSERT/REPLACE/SET/DELETE databases functions for JSONField
Django
-------------------------------------+-------------------------------------
Reporter: Baptiste | Owner: nobody
Mispelon |
Type: New | Status: new
feature |
Component: Database | Version: dev
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
//(I'm not sure if this has been discussed/rejected before. I couldn't
find references to it in ticket #12990 so I'm assuming it hasn't).//
== Background
I was trying to write an `update(...)` query that would modify a key on an
existing JSONField based on the value of another column.
I managed to solve it by writing a few database-specific `Func` [1] but it
was trickier than I'd anticipated and I think Django could make things a
bit simpler by providing some generic building blocks.
So I spent a bit of time comparing the respective documentations of the
four core database and came up with this list of four functions that I
think would be good candidates.
For the most part, they share a common name and API and they're also
pretty basic operations from which you can build more complex ones.
//(keep in mind I'm more familiar with Postgres than the others so I might
have gotten a few things wrong)//
== 1 - JSON_INSERT
Add a path+value to an object. Do nothing if the path already exists.
||= Backend =||= Syntax =||= //Notes// =||
|| PostgreSQL [2] || `jsonb_insert(object, path, value)` || ||
|| MariaDB/MySQL [3] || `JSON_INSERT(obj, path, value)` || //Supports
multiple path+value pairs// ||
|| Oracle [4] || `JSON_TRANSFORM(obj, SET path=value IGNORE ON EXISTING)`
|| ||
|| SQLite [5] || `json_insert(obj, path, value)` || //Supports multiple
path+value pairs// ||
== 2 - JSON_REPLACE
Update a path+value in an object. Do nothing if the path does not exist.
||= Backend =||= Syntax =||= //Notes// =||
|| PostgreSQL [6] || `jsonb_set(obj, path, value, false)` || ||
|| MariaDB/MySQL [7] || `JSON_REPLACE(obj, path, value)` || //Supports
multiple path+value pairs// ||
|| Oracle [4] || `JSON_TRANSFORM(obj, SET path=value IGNORE ON MISSING)`
|| ||
|| SQLite [8] || `json_replace(obj, path, value)` || //Supports multiple
path+value pairs// ||
== 2 - JSON_SET
Update a path+value in an object. Create it if the path does not exist.
||= Backend =||= Syntax =||= //Notes// =||
|| PostgreSQL [6] || `jsonb_set(obj, path, value, true)` || ||
|| MariaDB/MySQL [9] || `JSON_SET(obj, path, value)` || //Supports
multiple path+value pairs// ||
|| Oracle [4] || `JSON_TRANSFORM(obj, SET path=value)` || ||
|| SQLite [10] || `json_set(obj, path, value)` || //Supports multiple
path+value pairs// ||
== 4 - JSON_REMOVE
Remove a path from an object. Do nothing if the path does not exist.
||= Backend =||= Syntax =||= //Notes// =||
|| PostgreSQL [11] || `obj #- path` || ||
|| MariaDB/MySQL [12] || `JSON_REMOVE(obj, path)` || //Supports multiple
paths// ||
|| Oracle [4] || `JSON_TRANSFORM, REMOVE path IGNORE ON MISSING` || ||
|| SQLite [13] || `json_remove(obj, path)` || //Supports multiple paths//
||
----
[1] https://gist.github.com/bmispelon/d7f4ecc710a3c2f5c6b07ba5453c4398
[2] https://www.postgresql.org/docs/current/functions-
json.html#id-1.5.8.22.5.11.2.2.15.1.1.1
[3] https://dev.mysql.com/doc/refman/8.0/en/json-modification-
functions.html#function_json-insert
[4] https://docs.oracle.com/en/database/oracle/oracle-
database/21/sqlrf/JSON_TRANSFORM.html
[5] https://www.sqlite.org/json1.html#jins
[6] https://www.postgresql.org/docs/current/functions-
json.html#id-1.5.8.22.5.11.2.2.13.1.1.1
[7] https://dev.mysql.com/doc/refman/8.0/en/json-modification-
functions.html#function_json-replace
[8] https://www.sqlite.org/json1.html#jrepl
[9] https://dev.mysql.com/doc/refman/8.0/en/json-modification-
functions.html#function_json-set
[10] https://www.sqlite.org/json1.html#jset
[11] https://www.postgresql.org/docs/current/functions-json.html
#FUNCTIONS-JSONB-OP-TABLE
[12] https://dev.mysql.com/doc/refman/8.0/en/json-modification-
functions.html#function_json-remove
[13] https://www.sqlite.org/json1.html#jrm
--
Ticket URL: <https://code.djangoproject.com/ticket/32519>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Adam Johnson (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/32519#comment:1>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
Baptiste, Thanks for the ticket. I don't think we need to support all
these functions. I would rather focus on using key transforms in
`QuerySet.update()` to which we can use `JSON_SET()`, e.g.
{{{
Article.objects.update(details__headline='new text')
Article.objects.update(details__authors__0=F('author'))
}}}
`JSON_REPLACE()` and `JSON_INSERT()` can be emulated with this API by
filtering existing/not existing keys (see also similar ticket #30200 for
`ArrayField`.)
What do you think?
--
Ticket URL: <https://code.djangoproject.com/ticket/32519#comment:2>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Baptiste Mispelon):
**TL;DR**: I agree with reducing the scope of this ticket to only
`JSON_SET` and `JSON_REMOVE`
----
I hadn't thought of using key transforms to mimic `JSON_SET`. From a user
perspective it does seem nicer than the JSON path syntax (could be lack of
familiarity on my part).
However I think it would still be worthwhile to have a more low-level
database function like `JSON_SET` available.
It would allow some use-cases that can't be expressed as simple key
transforms: a dynamic key for example, or one that depends on other
fields.
As for insert/replace/set, I personally don't really have a usecase for
distinguishing between them.
While researching I saw that all 4 db engines made the distinction so I
figured it must have been useful for //somebody//.
But that's not a very convincing argument, so I wouldn't be opposed to
only having `JSON_SET` landed in core.
--
Ticket URL: <https://code.djangoproject.com/ticket/32519#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Baptiste Mispelon | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
Comment:
Agreed, we can add support for using key and path transforms in `update()`
for `JSONFields` and add `JSONSet()` as an option for complex use cases.
--
Ticket URL: <https://code.djangoproject.com/ticket/32519#comment:4>
Django
-------------------------------------+-------------------------------------
| munsch
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => allen-munsch
* status: new => assigned
Comment:
I have a draft PR currently open with a first attempt here:
* https://github.com/django/django/pull/15422
It currently has tests for sqlite and postgresql.
I think there is room for improvement in the signature, as their is a
kwarg that seems like it would be redundant.
For example the usage of "some_json_field" here:
# sqlite
queryset.update(some_json_field=JSONSet(field="some_json_field",
fields={"$.item_in_json_field": Value("asdf")}))
# postgresql
queryset.update(some_json_field=JSONSet(field="some_json_field",
fields={"{item_in_json_field}": Value('"asdf"')}))
There's also some backend vendor specific wonkiness on `F()`s and
`Value`s, and all of the cases on oracle for the op type.
I'm hoping to continue work on it this week and next.
My thinking currently is that it would be possible to connect JSONSet into
the JSONFields update key and transforms ( although I don't know what the
key and transform code looks like currently, and was going to look into
that this week)
Depending on direction from the core maintainers I could also attempt to
include a JSONRemove util, either in the same PR, or in a separate one.
--
Ticket URL: <https://code.djangoproject.com/ticket/32519#comment:5>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: allen-munsch => (none)
* status: assigned => new
--
Ticket URL: <https://code.djangoproject.com/ticket/32519#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Baptiste Mispelon | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Florian Demmer (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/32519#comment:7>
Django
-------------------------------------+-------------------------------------
| Turek
Type: New feature | Status: assigned
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: (none) => Kamil Turek
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/32519#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Baptiste Mispelon | Owner: Kamil
| Turek
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Sage Abdullah (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/32519#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: Baptiste Mispelon | Owner: Kamil
| Turek
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Clément Escolano (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/32519#comment:10>
Django
-------------------------------------+-------------------------------------
Reporter: Baptiste Mispelon | Owner: Kamil
| Turek
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: elonzh (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/32519#comment:11>
Django
-------------------------------------+-------------------------------------
Reporter: Baptiste Mispelon | Owner: Kamil
| Turek
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: şuayip üzülmez (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/32519#comment:12>
Django
-------------------------------------+-------------------------------------
Reporter: Baptiste Mispelon | Owner: Kamil
| Turek
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: John Speno (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/32519#comment:13>
Django
-------------------------------------+-------------------------------------
| Adzhani
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: Kamil Turek => Shafiya Adzhani
Comment:
I will work on this ticket during GSoC. Hopefully we will land these new
features soon!
--
Ticket URL: <https://code.djangoproject.com/ticket/32519#comment:14>