Collections:
JSON_INSERT() - Inserting Members to JSON Value
How to insert a new JSON child element to a given JSON (JavaScript Object Notation) value using the JSON_INSERT() function?
✍: FYIcenter.com
JSON_INSERT(json, path1, val1, path2, val2, ...) is a MySQL built-in function that inserts child members in a JSON value at given new locations. Each path-value argument pair specifies locations where new elements to be added and what JSON value to add. 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_INSERT(@j, '$.b[5]', 9); -- +------------------------------+ -- | JSON_INSERT(@j, '$.b[5]', 9) | -- +------------------------------+ -- | {"a": 1, "b": [2, 3, 9]} | -- +------------------------------+ SELECT JSON_INSERT(@j, '$.c', '[true, false]'); -- +---------------------------------------------+ -- | JSON_INSERT(@j, '$.c', '[true, false]') | -- +---------------------------------------------+ -- | {"a": 1, "b": [2, 3], "c": "[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": 1, "b": [2, 3]}'; SELECT JSON_INSERT(@j, '$.c', CAST('[true, false]' AS JSON)); -- +-------------------------------------------------------+ -- | JSON_INSERT(@j, '$.c', CAST('[true, false]' AS JSON)) | -- +-------------------------------------------------------+ -- | {"a": 1, "b": [2, 3], "c": [true, false]} | -- +-------------------------------------------------------+ SELECT JSON_INSERT(@j, '$.c', JSON_ARRAY(true, false)); -- +-------------------------------------------------+ -- | JSON_INSERT(@j, '$.c', JSON_ARRAY(true, false)) | -- +-------------------------------------------------+ -- | {"a": 1, "b": [2, 3], "c": [true, false]} | -- +-------------------------------------------------+ 1 row in set (0.01 sec) SELECT JSON_INSERT(@j, '$.c', JSON_OBJECT('PI',3.14)); -- +------------------------------------------------+ -- | JSON_INSERT(@j, '$.c', JSON_OBJECT('PI',3.14)) | -- +------------------------------------------------+ -- | {"a": 1, "b": [2, 3], "c": {"PI": 3.14}} | -- +------------------------------------------------+
Note that if a path argument specifies an existing location (an existing index of a JSON array or an existing key of a JSON object), the associated value will not be inserted. For example:
SET @j = '{ "a": 1, "b": [2, 3]}'; SELECT JSON_INSERT(@j, '$.a', 'FYI', '$.b[1]', 9); -- +--------------------------------------------+ -- | JSON_INSERT(@j, '$.a', 'FYI', '$.b[1]', 9) | -- +--------------------------------------------+ -- | {"a": 1, "b": [2, 3]} | -- +--------------------------------------------+
Reference information of the JSON_INSERT() function:
JSON_INSERT(json, path1, val1, path2, val2, ...): modjson Inserts child members in a JSON value at given new locations. which are specified by path arguments using the "$.key" or "$[i]" format. If a path argument specifies an existing location (an existing index of a JSON array or an existing key of a JSON object), the associated value will not be inserted. 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_KEYS() - Exacting Keys from JSON Object
⇐ JSON_STORAGE_FREE() - Free Space in JSON Value
2023-12-11, 199🔥, 0💬
Popular Posts:
Where to find answers to frequently asked questions on Transaction Management: Commit or Rollback in...
What is test testing area for? The testing area is provided to allow visitors to post testing commen...
How To Provide Default Values to Function Parameters in SQL Server Transact-SQL? If you add a parame...
Where to find Oracle database server tutorials? Here is a collection of tutorials, tips and FAQs for...
How to download and install SQL Server 2005 Sample Scripts in SQL Server? If you want to learn from ...