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, 269🔥, 0💬
Popular Posts:
How To Convert Character Strings into Numeric Values in SQL Server Transact-SQL? Sometimes you need ...
How To Locate and Take Substrings with CHARINDEX() and SUBSTRING() Functions in SQL Server Transact-...
How To End a Stored Procedure Properly in SQL Server Transact-SQL? Where the end of the "CREATE PROC...
How To Enter Unicode Character String Literals in SQL Server Transact-SQL? Unicode characters are mu...
Can Date and Time Values Be Converted into Integers in SQL Server Transact-SQL? Can date and time va...