#32519: Add INSERT/REPLACE/SET/DELETE databases functions for JSONField
-------------------------------------+-------------------------------------
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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/052.86a927d31bf370913cc92b8602197b49%40djangoproject.com.
