JSON_ARRAY scalar function

The JSON_ARRAY function generates a JSON array by explicitly listing the array elements by using JSON-expression, or by using a query.

Read syntax diagramSkip visual syntax diagramJSON_ARRAY(,JSON-expressionFORMATJSONBSONfullselectFORMATJSONBSONABSENT ON NULLNULL ON NULLRETURNING CLOB(2G) FORMAT JSONRETURNINGdata-typeFORMAT JSONENCODING UTF8)
data-type
Read syntax diagramSkip visual syntax diagramCHARACTERCHAR(1)( integer)CHARACTERCHARVARYINGVARCHAR( integer)CHARACTERCHARLARGE OBJECTCLOB(1M)( integerKMG)BINARY VARYINGVARBINARY(integer)BINARY LARGE OBJECTBLOB(1M)( integerKMG)

Although the schema for this function is SYSIBM, the function cannot be specified as a qualified name.

JSON-expression
The expression that is used to generate a value in the JSON array.
The result type of this expression can be any built-in data type, except the following data types (SQLSTATE 42815):
  • GRAPHIC
  • VARGRAPHIC
  • DBCLOB
  • BINARY
  • CHAR FOR BIT DATA
  • VARCHAR FOR BIT DATA
  • XML
  • A user-defined type that is sourced on any of the previously listed data types

If the generated value is numeric, it cannot be Infinity, NaN, or sNaN (SQLSTATE 22023).

If FORMAT JSON or FORMAT BSON is not specified, and the generated value is not numeric, any special characters (for example, backslash or double quotation marks) within the result string are escaped.

If FORMAT JSON or FORMAT BSON is not specified and JSON-expression is binary string type, it is interpreted as FORMAT BSON.

FORMAT JSON
JSON-expression is formatted as JSON data.

If JSON-expression is a character string data type, it is treated as JSON data.

If JSON-expression is a binary string data type, it is interpreted as UTF-8 data.

FORMAT BSON
Specifies that JSON-expression is formatted as the BSON representation of JSON data (SQLSTATE 22032). JSON-expression must be a binary string data type (SQLSTATE 42815).
fullselect
Specifies a fullselect that returns a single column to be used to generate the values in the array (SQLSTATE 42823). The value of each row is used to generate a value in the JSON array.
The result type of this column cannot be any of the following data types (SQLSTATE 42815):
  • GRAPHIC
  • VARGRAPHIC
  • DBCLOB
  • BINARY
  • CHAR FOR BIT DATA
  • VARCHAR FOR BIT DATA
  • XML
  • A user-defined type that is sourced on any of the previously listed data types

If the generated value is numeric, it cannot be Infinity, NaN, or sNaN (SQLSTATE 22023).

If FORMAT JSON or FORMAT BSON is not specified and the generated value is not numeric, any special characters (for example, backslash or double quotation marks) within the result string are escaped.

If FORMAT JSON or FORMAT BSON is not specified and the fullselect is binary string type, it is interpreted as FORMAT BSON.

FORMAT JSON
fullselect is formatted as JSON data.

If fullselect is a character string data type, it is treated as JSON data.

If fullselect is a binary string data type, it is interpreted as UTF-8 data.

FORMAT BSON
Specifies that fullselect is formatted as the BSON representation of JSON data (SQLSTATE 22032). fullselect must be a binary string data type (SQLSTATE 42815).
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 clause is the default.
NULL ON NULL
A null array element is included in the JSON array.
RETURNING data-type
The data type of the result can be CHAR, VARCHAR, CLOB, VARBINARY, or BLOB (SQLSTATE 42815). The default is CLOB (2 GB).

See CREATE TABLE statement for the description of built-in data types.

FORMAT JSON
The returned data is formatted as JSON data.
ENCODING UTF8
Specifies the encoding to use when data-type is a binary string type. This clause is allowed only for binary string types. The default for binary strings is UTF-8.

Notes

  • If parameter markers are not explicitly cast to a supported data type, an error is returned (SQLSTATE 42815)

Examples

  1. Generate a JSON array that contains the values 'Washington', 'Jefferson', and 'Hamilton'.
    VALUES JSON_ARRAY('Washington', 'Jefferson', 'Hamilton');

1
-------------------------------------
["Washington","Jefferson","Hamilton"]
  2. Generate a JSON array that includes all department numbers.
    VALUES JSON_ARRAY(SELECT DEPTNO FROM DEPT);

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