Opened 4 years ago
Last modified 2 days ago
#32519 assigned New feature
Add support for using key and path transforms in update() for JSONFields.
Reported by: | Baptiste Mispelon | Owned by: | Shafiya Adzhani |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | |
Cc: | Adam Johnson, Florian Demmer, Sage Abdullah, Clément Escolano, elonzh, şuayip üzülmez, John Speno, Carles Pina Estany, Simon Charette | Triage Stage: | Accepted |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | yes |
Easy pickings: | no | UI/UX: | no |
Description
(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
Change History (19)
comment:1 by , 4 years ago
Cc: | added |
---|
follow-up: 3 comment:2 by , 4 years ago
comment:3 by , 4 years ago
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.
comment:4 by , 4 years ago
Summary: | Add INSERT/REPLACE/SET/DELETE databases functions for JSONField → Add support for using key and path transforms in update() for JSONFields. |
---|---|
Triage Stage: | Unreviewed → Accepted |
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.
comment:5 by , 3 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
I have a draft PR currently open with a first attempt here:
- https://github.com/django/django/pull/15422
- ( planning to improve the PR formatting for this, still learning the preferred format )
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.
comment:6 by , 3 years ago
Owner: | removed |
---|---|
Status: | assigned → new |
comment:7 by , 3 years ago
Cc: | added |
---|
comment:8 by , 2 years ago
Owner: | set to |
---|---|
Status: | new → assigned |
comment:9 by , 2 years ago
Cc: | added |
---|
comment:10 by , 20 months ago
Cc: | added |
---|
comment:11 by , 14 months ago
Cc: | added |
---|
comment:12 by , 10 months ago
Cc: | added |
---|
comment:13 by , 6 months ago
Cc: | added |
---|
comment:14 by , 6 months ago
Owner: | changed from | to
---|
I will work on this ticket during GSoC. Hopefully we will land these new features soon!
comment:15 by , 3 months ago
Has patch: | set |
---|
Here is the patch: https://github.com/django/django/pull/18489
comment:16 by , 2 months ago
Patch needs improvement: | set |
---|
comment:17 by , 2 months ago
Cc: | added |
---|
comment:18 by , 3 days ago
Patch needs improvement: | unset |
---|
As discussed in the PR, we are going to split the work for this ticket into two parts:
- Adding JSONSet and JSONRemove
- Adding support for using key and path transforms in update()
The latter requires non-trivial amount of refactoring in some hot code paths of the ORM. We think it would be best to get the JSONSet and JSONRemove functions in first and "let them simmer for a release". Once people have got the chance to play around with the functions, we will revisit the transform support later.
Here is the link for new PR: https://github.com/django/django/pull/18758
comment:19 by , 2 days ago
Cc: | added |
---|---|
Patch needs improvement: | set |
Left a few comments on PR.
It's in a relatively good shape but there are possible SQL injection vectors with keys and while the documentation points out that JSONSet
can be used instead of directly altering a JSONField
value it doesn't explain why someone would want to do this.
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 useJSON_SET()
, e.g.JSON_REPLACE()
andJSON_INSERT()
can be emulated with this API by filtering existing/not existing keys (see also similar ticket #30200 forArrayField
.)What do you think?