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, 200🔥, 0💬
Popular Posts:
How To Connect the Oracle Server as SYSDBA in Oracle? This is Step 4. The best way to connect to the...
How to set database to be READ_ONLY in SQL Server? Databases in SQL Server have two update options: ...
How To Calculate Age in Days, Hours and Minutes in SQL Server Transact-SQL? On many Web sites, news ...
How To Divide Query Output into Multiple Groups with the GROUP BY Clause in SQL Server? Sometimes, y...
Where to find answers to frequently asked questions on Downloading and Installing SQL Server 2005 Ex...