The result of combining two JSON documents is a JSON document containing data from both the input documents. The resulting document is structured as a JSON object or a JSON array. The JSON documents being combined can be structured differently than each other. For example, a JSON document structured as an array can be combined with a JSON document structured as an object, with the resulting combination structured as a JSON array. The following explains the result of various combinations.
'ARRAY' Parameter is Specified in the Command
- If both input JSON documents are structured as JSON arrays, the result is an array with values from each JSON document.
For example, if j1 = [1,2] and j2 = [3,4], the combination is [1,2,3,4].
- If one of the JSON documents is structured as a JSON array and the other is structured as a JSON object, the result is a JSON array composed of all the elements of the JSON document structured as an array, plus one more element, which is the entire JSON document structured as an object (the second document).
For example, if j1 = [1,2] and j2 = {"name" : "Jane"}, the combination is [1,2, { "name" : "Jane" } ].
- If both JSON documents are structured as JSON objects, the result is a JSON document structured as an array composed of JSON documents structured as objects from each JSON object being combined.
For example, if j1 = { "name" : "Harry" } and j2 = { "name" : "Jane" }, the combination is [ { "name" : "Harry" }, {"name" : "Jane"} ].
'OBJECT' Parameter is Specified in the Command
The result is a combined JSON document structured as an object containing all the members of each input object.
For example, if j1 = { "name" : "Jane" , "age" : "30" } and j2 = { "name" : "Harry", "age" : "41" }, the combination is { "name" : "Jane" , "age" : "30" , "name" : "Harry" , "age" : "41" }
If either JSON document being combined is structured as an array an error is reported.
'ARRAY' or 'OBJECT' is Not Specified in the Command
- If both JSON documents are structured as JSON arrays, the result is a JSON document structured as an array with values from each JSON document.
- If either JSON document is structured as an array, the result is a JSON document structured as an array, which is composed of all the elements of the JSON document which is an array, plus one more element which is the entire JSON document structured as an object. This is the same behavior as specifying 'ARRAY'.
- If both JSON documents are structured as objects, the result is a combined JSON document structured as an object containing all the members of each input object. This is the same behavior as specifying 'OBJECT'.
COMBINE and NULL Parameters
- If one of the JSON documents being combined is NULL, the result is a non-NULL JSON document.
- If both JSON documents being combined are NULL, the result is a
Vantage NULL.
COMBINE and CHARACTER SET
- If one of the JSON documents being combined has CHARACTER SET UNICODE, the resulting JSON instance has text in CHARACTER SET UNICODE.
- If both JSON documents being combined has text in CHARACTER SET LATIN, the resulting JSON instance has text in CHARACTER SET LATIN.
COMBINE and Storage Format
JSON data stored in any storage format may be combined with JSON data stored in any other format.
- If both JSON documents being combined are stored as text, the result is a JSON document stored as text with the character set defined as in the previous section.
- If one of the JSON documents is stored as text and the other is stored in a binary format, the result is a JSON document stored as text in CHARACTER SET UNICODE.
- If one of the JSON documents is stored as BSON and the other is stored as UBJSON, the result is a JSON document stored in BSON format.
- If both documents are stored in UBJSON format, the result is a JSON document stored in UBJSON format.
Rules and Restrictions
An error occurs if the result of the combination is too large. The result cannot exceed the maximum length for the JSON type. For more information about the maximum length, see Maximum Length of a JSON Instance.
If the result of the Combine method will be inserted into a column of a table or used as a parameter to a UDF, UDM, stored procedure, or external stored procedure, the resulting length is subject to the maximum length of that defined JSON instance. If it is too large, an error will result.