Add support for using key and path transforms in update() for JSONFields.

[Baptiste Mispelon]

(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

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

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

[Mariusz Felisiak]

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.

[allen-munsch]

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 Values, 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.