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.

data-type

ccsid-clause

normalize-clause

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.

If neither FORMAT JSON nor FORMAT BSON is specified:

If JSON-expression is one of the built-in functions JSON_ARRAY, JSON_OBJECT, JSON_QUERY, JSON_ARRAYAGG, or JSON_OBJECTAGG, the explicit or implicit FORMAT value of the function's RETURNING clause determines the format of JSON-expression.
If JSON-expression is a binary string type, it is interpreted as FORMAT BSON.
Otherwise, JSON-expression or fullselect is considered unformatted data. If the generated value is not numeric, the result string will be constructed with strings enclosed in quotes and any special characters will be escaped. A numeric value that is not a valid JSON number, such as INFINITY or NAN, will result in an error.

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.
```
VALUES (JSON_ARRAY('Washington', 'Jefferson', 'Hamilton'));
```
The result is the following JSON array:
```
["Washington","Jefferson","Hamilton"]
```

Generate a JSON array that includes all the department numbers.

VALUES(JSON_ARRAY((SELECT DEPTNO FROM DEPT 
                          WHERE DEPTNAME LIKE 'BRANCH OFFICE%' )));

The result is the following JSON array:

["F22","G22","H22","I22","J22"]