
JSON_ARRAY
The JSON_ARRAY function generates a JSON array either by explicitly listing the array elements or by using a query. If no JSON-expression is provided, the fullselect returns no values, or all values are null and ABSENT ON NULL is specified, an empty array is returned.
- JSON-expression
- The expression to use to generate a value in the JSON array. The result type of this expression can be any built-in data type except XML, ROWID, or DATALINK. It cannot be CHAR or VARCHAR bit data. It cannot be a user-defined type that is sourced on any of these data types.
- fullselect
- Specifies a fullselect that returns a single column to be used to generate the values in the array. The value of each row is used to generate a value in the JSON array. The result type of this column can be any built-in data type except XML, ROWID, DATALINK, or a user-defined type that is sourced on any of these data types. It cannot be CHAR or VARCHAR bit data.
- FORMAT JSON or FORMAT BSON
- Specifies whether JSON-expression or fullselect is already formatted data.
- FORMAT JSON
- JSON-expression or fullselect is formatted as JSON data. If JSON-expression or fullselect is a character or graphic string data type, it is treated as JSON data. If JSON-expression or fullselect is a binary string data type, it is interpreted as UTF-8 or UTF-16 data.
- FORMAT BSON
- JSON-expression or fullselect is formatted as the BSON representation of JSON data and must be a binary string data type.
- ABSENT ON NULL or NULL ON NULL
- Specifies what to return when an array element produced by JSON-expression or
fullselect is the null value.
- ABSENT ON NULL
- A null array element is not included in the JSON array. This is the default.
- NULL ON NULL
- A null array element is included in the JSON array.
- RETURNING data-type
- Specifies the format of the result.
- data-type
- The data type of the result. For CHAR and VARCHAR results, the CCSID cannot be 65535. The
default is CLOB(2G) CCSID 1208.
If a CCSID is specified and the data-type is GRAPHIC, VARGRAPHIC, or DBCLOB, the CCSID must be a Unicode CCSID.
If the CCSID attribute is not specified, the CCSID is determined as described in CAST specification.
- FORMAT JSON
- JSON data is returned as a JSON string.
- ENCODING UTF8 or ENCODING UTF16
- The encoding to use when data-type is a binary string type. This clause is only allowed for binary string types. The default for binary strings is UTF8.
Examples
- Generate a JSON array containing the values Washington, Jefferson,
and Hamilton.
The result is the following JSON array:VALUES (JSON_ARRAY('Washington', 'Jefferson', 'Hamilton'));
["Washington","Jefferson","Hamilton"]
- Generate a JSON array that includes all the department
numbers.
The result is the following JSON array:VALUES(JSON_ARRAY((SELECT DEPTNO FROM DEPT WHERE DEPTNAME LIKE 'BRANCH OFFICE%' )));
["F22","G22","H22","I22","J22"]
