Collections:
JSON_SET() - Inserting/updating JSON Child Members
How to insert or update child members of a JSON (JavaScript Object Notation) value using the JSON_SET() function?
✍: FYIcenter.com
JSON_SET(json, path1, val1, path2, val2, ...) is a MySQL built-in function that
inserts child members of a JSON value at given new locations,
and replaces child numbers at given existing locations.
The path argument uses the "$", "$.key" or "$[i]" format.
The value can be a JSON scalar (String, Number, Boolean, or Null),
a JSON array, or a JSON object.
JSON scalars can be specified as literals of MySQL equivalent data types. For example:
SET @j = '{ "a": 1, "b": [2, 3]}'; SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]'); -- +-------------------------------------------------+ -- | JSON_SET(@j, '$.a', 10, '$.c', '[true, false]') | -- +-------------------------------------------------+ -- | {"a": 10, "b": [2, 3], "c": "[true, false]"} | -- +-------------------------------------------------+ SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]'); -- +----------------------------------------------------+ -- | JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') | -- +----------------------------------------------------+ -- | {"a": 1, "b": [2, 3], "c": "[true, false]"} | -- +----------------------------------------------------+ SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]'); -- +-----------------------------------------------------+ -- | JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') | -- +-----------------------------------------------------+ -- | {"a": 10, "b": [2, 3]} | -- +-----------------------------------------------------+ SELECT JSON_REPLACE(@j, '$', '[true, false]'); -- +----------------------------------------+ -- | JSON_REPLACE(@j, '$', '[true, false]') | -- +----------------------------------------+ -- | "[true, false]" | -- +----------------------------------------+
However, JSON arrays and JSON objects need to be constructed with CAST(), JSON_ARRAY(), or JSON_OBJECT() functions. For example:
SET @j = '["a", {"b": [1, 2]}, [3, 4]]'; SELECT JSON_SET(@j, '$[2]', CAST('[true, false]' AS JSON)); -- +-----------------------------------------------------+ -- | JSON_SET(@j, '$[2]', CAST('[true, false]' AS JSON)) | -- +-----------------------------------------------------+ -- | ["a", {"b": [1, 2]}, [true, false]] | -- +-----------------------------------------------------+ SELECT JSON_SET(@j, '$[1].b[9]', JSON_ARRAY(true, false)); -- +----------------------------------------------------+ -- | JSON_SET(@j, '$[1].b[9]', JSON_ARRAY(true, false)) | -- +----------------------------------------------------+ -- | ["a", {"b": [1, 2, [true, false]]}, [3, 4]] | -- +----------------------------------------------------+ SELECT JSON_SET(@j, '$[1].b[9]', JSON_OBJECT('PI',3.14)); -- +---------------------------------------------------+ -- | JSON_SET(@j, '$[1].b[9]', JSON_OBJECT('PI',3.14)) | -- +---------------------------------------------------+ -- | ["a", {"b": [1, 2, {"PI": 3.14}]}, [3, 4]] | -- +---------------------------------------------------+
Reference information of the JSON_SET() function:
JSON_SET(json, path1, val1, path2, val2, ...): modjson Replaces child members of a JSON value at given locations, which are specified by path arguments in the "$", "$.key" or "$[i]" format. Arguments, return value and availability: json: Required. The JSON value to be updated. path1, val1, path2, val2, ...: One or more JSON path-value pairs to be processed. modjson: Return value. The updated JSON value. Available since MySQL 5.7.
Related MySQL functions:
⇒ JSON_STORAGE_SIZE() - Storage Size of JSON Value
⇐ JSON_SEARCH() - Searching String in JSON
2023-12-11, 549🔥, 0💬
Popular Posts:
Where to find MySQL database server tutorials? Here is a collection of tutorials, tips and FAQs for ...
How To Get the Definition of a User Defined Function Back in SQL Server Transact-SQL? If you want ge...
Where to find answers to frequently asked questions on INSERT, UPDATE and DELETE Statements in MySQL...
What Is "mysqld" in MySQL? "mysqld" is MySQL server daemon program which runs quietly in background ...
How To Find Out What Privileges a User Currently Has in Oracle? Privileges granted to users are list...