Purpose
The NEW JSON constructor allocates a JSON type instance. The constructor can be used without arguments (default constructor) or with arguments to set the value, the character set, or storage format of the JSON instance. The resulting JSON instance can be used to insert a JSON type into a column or as a JSON type argument to a function or method.
Syntax
Syntax Elements
- 'JSON_String'
- A text string that will be the value of the resulting JSON instance.
- LATIN or UNICODE
- The character set of the resulting JSON instance.
- BSON or UBJSON
- The storage format of the resulting JSON instance.
- JSON_binary_data
- JSON binary data that will be the value of the resulting JSON instance.
Rules and Restrictions
- 16776192 LATIN characters or 8388096 UNICODE characters for JSON text data
- 16776192 bytes for binary data
If the JSON type is being inserted into a column or used as an argument to a function or method, a check is performed to ensure that the actual length of the JSON data is less than or equal to the maximum length specified for this particular instance, since it could be less than the absolute maximum. In any case where the data is too large for its current usage, an error is reported. Note that truncation does not occur.
Usage Notes
In the default constructor, no arguments are passed to the constructor expression. NEW JSON() initializes an empty JSON type value with the character set based on the character set of the user. The data is set to a null string, and the default storage format is text.
If you pass an empty JSON object as an argument to the constructor, for example, NEW JSON(''), the constructor returns an empty JSON object.
You can use the NEW JSON constructor to construct a JSON document that is stored in a binary format; however, if the result of this constructor is sent to a client, it will be sent as a CLOB which contains the text representation of the binary data.
You can append a JSON entity reference to the end of a constructor expression as described in JSON Entity Reference.
Example: Default JSON Constructor
NEW JSON();
Examples: JSON Constructor with Text String Arguments
NEW JSON ('{"name" : "cameron", "age" : 24}')
NEW JSON ('{"name" : "cameron", "age" : 24}', LATIN)
Example: JSON Constructor with Binary Data Arguments
- 0x00000016: Overall length of the BSON data
- 0x02: Indicates that the data following the key is of type 'string'
- 0x68, 0x65, 0x6C, 0x6C, 0x6F, 0x00: The null-terminated string 'hello', interpreted as the key
- 0x00000006: The length of the string value that follows the key
- 0x77, 0x6F, 0x72, 0x6C, 0x64, 0x00: The null-terminated string 'world', interpreted as the value
- 0x00: The null-terminator to indicate the end of the document
The data represents a JSON document that looks like the following in text format:
{"hello":"world"}
/*Creates a JSON document stored as BSON, explicitly*/
SELECT NEW JSON ('160000000268656C6C6F0006000000776F726C640000'xb, BSON);