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, 241🔥, 0💬
Popular Posts:
How Fixed Length Strings Are Truncated and Padded in SQL Server Transact-SQL? When the length of the...
How To Change the Name of a Database User in SQL Server? If you want to change the name of an existi...
What Is a Parameter File in Oracle? A parameter file is a file that contains a list of initializatio...
How To Get Year, Month and Day Out of DATETIME Values in SQL Server Transact-SQL? You can use DATEPA...
How To Convert Numeric Values to Character Strings in MySQL? You can convert numeric values to chara...