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, 287🔥, 0💬
Popular Posts:
How To Verify Your PHP Installation in MySQL? PHP provides two execution interfaces: Command Line In...
How to change the data type of an existing column with "ALTER TABLE" statements in SQL Server? Somet...
What Happens If the UPDATE Subquery Returns Multiple Rows in MySQL? If a subquery is used in a UPDAT...
How To Convert Numeric Expression Data Types using the CONVERT() Function in SQL Server Transact-SQL...
Where to find answers to frequently asked questions on Managing Security, Login and User in SQL Serv...