Collections:
JSON_MERGE_PRESERVE() - Merging JSON with All Members
How to merge multiple JSON (JavaScript Object Notation) values with all members preserved using the JSON_MERGE_PRESERVE() function?
✍: FYIcenter.com
JSON_MERGE_PRESERVE(json1, json2, ...) is a MySQL built-in function that merges a list of JSON values into a single JSON value with all members preserved. A JSON value can be a JSON scalar (String, Number, Boolean, or Null), a JSON array, or a JSON object.
Specified JSON value arguments are merged one at a time from left to right according to the following rules:
JSON value arguments must be specified as JSON encoded strings or constructed with CAST(), JSON_MERGE_PRESERVE(), or JSON_OBJECT() functions. For example:
SELECT JSON_MERGE_PRESERVE('[1, 2]', '[true, false]'); -- +------------------------------------------------+ -- | JSON_MERGE_PRESERVE('[1, 2]', '[true, false]') | -- +------------------------------------------------+ -- | [1, 2, true, false] | -- +------------------------------------------------+ SELECT JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}'); -- +----------------------------------------------------+ -- | JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}') | -- +----------------------------------------------------+ -- | {"id": 47, "name": "x"} | -- +----------------------------------------------------+ SELECT JSON_MERGE_PRESERVE('1', 'true'); -- +----------------------------------+ -- | JSON_MERGE_PRESERVE('1', 'true') | -- +----------------------------------+ -- | [1, true] | -- +----------------------------------+ SELECT JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}'); -- +---------------------------------------------+ -- | JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}') | -- +---------------------------------------------+ -- | [1, 2, {"id": 47}] | -- +---------------------------------------------+ SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }', '{ "a": 3, "c": 4 }'); -- +-----------------------------------------------------------------+ -- | JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }', '{ "a": 3, "c": 4 }') | -- +-----------------------------------------------------------------+ -- | {"a": [1, 3], "b": 2, "c": 4} | -- +-----------------------------------------------------------------+
Note that JSON scalar value arguments can not be specified as literals of equivalent MySQL data types. For example:
SELECT JSON_MERGE_PRESERVE('"x"', '99'); -- +----------------------------------+ -- | JSON_MERGE_PRESERVE('"x"', '99') | -- +----------------------------------+ -- | ["x", 99] | -- +----------------------------------+ SELECT JSON_MERGE_PRESERVE('"x"', 99); ERROR 3146 (22032): Invalid data type for JSON data in argument 2 to function json_merge_preserve; a JSON string or JSON type is required.
Reference information of the JSON_MERGE_PRESERVE() function:
JSON_MERGE_PRESERVE(json1, json2, ...): json Returns a JSON value by merging all JSON values given in the argument list with all members preserved. JSON scalars are wrapped as JSON arrays automatically. JSON objects are wrapped as JSON arrays only when merging with JSON arrays. JSON object members are merged if they have the same key. Arguments, return value and availability: json1, json2, ...: One or more JSON values to be merged. json: Return value. The merged JSON value. Available since MySQL 5.7.
Related MySQL functions:
⇒ JSON_OBJECT() - Creating JSON Object
⇐ JSON_MERGE_PATCH() - Merging JSON by Replacing Members
2023-12-11, 237🔥, 0💬
Popular Posts:
How To Enter Unicode Character String Literals in SQL Server Transact-SQL? Unicode characters are mu...
What Is an Oracle Tablespace in Oracle? An Oracle tablespace is a big unit of logical storage in an ...
How To Verify Your PHP Installation in MySQL? PHP provides two execution interfaces: Command Line In...
How To Convert Numeric Expression Data Types using the CAST() Function in SQL Server Transact-SQL? I...
How To Use GO Command in "sqlcmd" in SQL Server? "sqlcmd" is a command line client application to ru...