Collections:
JSON_ARRAY_APPEND() - Appending Value to JSON Array
How to append a new JSON value to a given JSON (JavaScript Object Notation) array using the JSON_ARRAY_APPEND() function?
✍: FYIcenter.com
JSON_ARRAY_APPEND(json, path1, val1, path2, val2, ...) is a MySQL built-in function that appends new JSON values to child arrays of a given JSON value. Each path-value argument pair specifies which JSON child array to be appended and what JSON value to append. 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", "c"], "d"]'; SELECT JSON_ARRAY_APPEND(@j, '$', 'end'); -- +-----------------------------------+ -- | JSON_ARRAY_APPEND(@j, '$', 'end') | -- +-----------------------------------+ -- | ["a", ["b", "c"], "d", "end"] | -- +-----------------------------------+ SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1); -- +----------------------------------+ -- | JSON_ARRAY_APPEND(@j, '$[1]', 1) | -- +----------------------------------+ -- | ["a", ["b", "c", 1], "d"] | -- +----------------------------------+ SET @j = '{"a": 1, "b": [2, 3], "c": 4}'; SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x'); -- +------------------------------------+ -- | JSON_ARRAY_APPEND(@j, '$.b', 'x') | -- +------------------------------------+ -- | {"a": 1, "b": [2, 3, "x"], "c": 4} | -- +------------------------------------+
However, JSON arrays and JSON objects need to be constructed with CAST(), JSON_ARRAY(), or JSON_OBJECT() functions. For example:
SET @j = '["a", ["b", "c"], "d"]'; SELECT JSON_ARRAY_APPEND(@j, '$', CAST('[1,2,3]' AS JSON)); -- +-----------------------------------------------------+ -- | JSON_ARRAY_APPEND(@j, '$', CAST('[1,2,3]' AS JSON)) | -- +-----------------------------------------------------+ -- | ["a", ["b", "c"], "d", [1, 2, 3]] | -- +-----------------------------------------------------+ SELECT JSON_ARRAY_APPEND(@j, '$', JSON_ARRAY(1,2,3)); -- +-----------------------------------------------+ -- | JSON_ARRAY_APPEND(@j, '$', JSON_ARRAY(1,2,3)) | -- +-----------------------------------------------+ -- | ["a", ["b", "c"], "d", [1, 2, 3]] | -- +-----------------------------------------------+ SELECT JSON_ARRAY_APPEND(@j, '$', JSON_OBJECT('End','Yes')); -- +------------------------------------------------------+ -- | JSON_ARRAY_APPEND(@j, '$', JSON_OBJECT('End','Yes')) | -- +------------------------------------------------------+ -- | ["a", ["b", "c"], "d", {"End": "Yes"}] | -- +------------------------------------------------------+
Note that if the path argument points to scalar element, it will be converted into array first and appended with the new value. For example:
SET @j = '["a", ["b", "c"], "d"]'; SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3); -- +-------------------------------------+ -- | JSON_ARRAY_APPEND(@j, '$[1][0]', 3) | -- +-------------------------------------+ -- | ["a", [["b", 3], "c"], "d"] | -- +-------------------------------------+ SET @j = '{"a": 1}'; SELECT JSON_ARRAY_APPEND(@j, '$', 'z'); -- +---------------------------------+ -- | JSON_ARRAY_APPEND(@j, '$', 'z') | -- +---------------------------------+ -- | [{"a": 1}, "z"] | -- +---------------------------------+
Reference information of the JSON_ARRAY_APPEND() function:
JSON_ARRAY_APPEND(json, path1, val1, path2, val2, ...): modjson Appends new JSON values to child arrays of a given JSON value. Each path-value argument pair specifies which JSON child array to be appended and what JSON value to append. The path argument uses 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.
⇒ JSON_ARRAY_INSERT() - Inserting Value to JSON Array
⇐ JSON_ARRAY() - Creating JSON Array
2023-12-12, 238🔥, 0💬
Popular Posts:
How To Drop an Index in Oracle? If you don't need an existing index any more, you should delete it w...
How to execute statements under given conditions in SQL Server Transact-SQL? How to use IF ... ELSE ...
What are binary literals supported in SQL Server Transact-SQL? Binary literals in Transact-SQL are s...
What Is the Difference Between GETDATE() and GETUTCDATE() in SQL Server Transact-SQL? The difference...
What Happens If the UPDATE Subquery Returns Multiple Rows in MySQL? If a subquery is used in a UPDAT...