Collections:
JSON_REPLACE() - Replacing JSON Child Members
How to replace child members of a JSON (JavaScript Object Notation) value using the JSON_REPLACE() function?
✍: FYIcenter.com
JSON_REPLACE(json, path1, val1, path2, val2, ...) is a MySQL built-in function that
replaces child members of a JSON value at given locations.
Each path-value argument pair specifies existing member locations
and replace-by values. Non-existing member locations are ignored.
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", {"b": [1, 2]}, [3, 4]]'; SELECT JSON_REPLACE(@j, '$[0]', 10, '$[2]', '[true, false]'); -- +-------------------------------------------------------+ -- | JSON_REPLACE(@j, '$[0]', 10, '$[2]', '[true, false]') | -- +-------------------------------------------------------+ -- | [10, {"b": [1, 2]}, "[true, false]"] | -- +-------------------------------------------------------+ SELECT JSON_REPLACE(@j, '$[1].b', '[true, false]', '$[9]', 'End'); -- +------------------------------------------------------------+ -- | JSON_REPLACE(@j, '$[1].b', '[true, false]', '$[9]', 'End') | -- +------------------------------------------------------------+ -- | ["a", {"b": "[true, false]"}, [3, 4]] | -- +------------------------------------------------------------+ 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_REPLACE(@j, '$[2]', CAST('[true, false]' AS JSON)); -- +---------------------------------------------------------+ -- | JSON_REPLACE(@j, '$[2]', CAST('[true, false]' AS JSON)) | -- +---------------------------------------------------------+ -- | ["a", {"b": [1, 2]}, [true, false]] | -- +---------------------------------------------------------+ SELECT JSON_REPLACE(@j, '$[2]', JSON_ARRAY(true, false)); -- +---------------------------------------------------+ -- | JSON_REPLACE(@j, '$[2]', JSON_ARRAY(true, false)) | -- +---------------------------------------------------+ -- | ["a", {"b": [1, 2]}, [true, false]] | -- +---------------------------------------------------+ SELECT JSON_REPLACE(@j, '$[2]', JSON_OBJECT('PI',3.14)); -- +--------------------------------------------------+ -- | JSON_REPLACE(@j, '$[2]', JSON_OBJECT('PI',3.14)) | -- +--------------------------------------------------+ -- | ["a", {"b": [1, 2]}, {"PI": 3.14}] | -- +--------------------------------------------------+
Reference information of the JSON_REPLACE() function:
JSON_REPLACE(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_SCHEMA_VALID() - JSON Schema Validation
⇐ JSON_REMOVE() - Removing JSON Child Members
2023-12-11, 455🔥, 0💬
Popular Posts:
Collections: Interview Questions MySQL Tutorials MySQL Functions Oracle Tutorials SQL Server Tutoria...
How To Divide Query Output into Multiple Groups with the GROUP BY Clause in SQL Server? Sometimes, y...
How To Connect ASP Pages to Oracle Servers in Oracle? If you are running Windows IIS Web server and ...
How To Get the Definition of a User Defined Function Back in SQL Server Transact-SQL? If you want ge...
How To Recover a Dropped Index in Oracle? If you have the recycle bin feature turned on, dropped ind...