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, 365🔥, 0💬
Popular Posts:
Where to find answers to frequently asked questions on Managing Security, Login and User in SQL Serv...
What Happens to Your Transactions When ERROR 1205 Occurred in MySQL? If your transaction receives th...
How To Get Year, Month and Day Out of DATETIME Values in SQL Server Transact-SQL? You can use DATEPA...
Is PL/SQL Language Case Sensitive in Oracle? PL/SQL language is not case sensitive: Reserved words a...
How To Count Rows with the COUNT(*) Function in SQL Server? If you want to count the number of rows,...