id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
32519	Add support for using key and path transforms in update() for JSONFields.	Baptiste Mispelon	Shafiya Adzhani	"//(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"	New feature	assigned	Database layer (models, ORM)	dev	Normal			Adam Johnson Florian Demmer Sage Abdullah Clément Escolano elonzh şuayip üzülmez John Speno Carles Pina Estany Simon Charette	Accepted	1	0	0	1	0	0