JSON_ARRAYAGG

The JSON_ARRAYAGG function aggregates items into a JSON array string.

Features

• Arbitrary expressions: Item expressions can be arbitrary, including other JSON functions.
• Null handling: Supports ON NULL behavior to define how to handle NULL values.
• Default behavior: If ON NULL is omitted, ABSENT ON NULL is the default.

Supported Version

• VERA Engine 4.3 or later.

Syntax

JSON_ARRAYAGG(items [ { NULL | ABSENT } ON NULL ])

Input Parameters

Parameter	Data type	Description
items	Any	The item expression to aggregate into the JSON array.
ON NULL	Keyword	Defines the behavior when a value is NULL. Options are NULL ON NULL or ABSENT ON NULL (default).

Examples

The following example shows how to use JSON_ARRAYAGG to aggregate values from a table into a JSON array string.

1. Create a Source Table

CREATE TEMPORARY TABLE src (
  n INT
) WITH (
  'connector' = 'datagen',
  'number-of-rows' = '5',
  'fields.n.kind' = 'sequence',
  'fields.n.start' = '1',
  'fields.n.end' = '5'
);

2. Aggregate Items into a JSON Array

SELECT CAST(JSON_ARRAYAGG(n) AS STRING) FROM src;

Output Result

The function creates a JSON array of the values in the table.

["1","2","3","4","5"]

Limitations

The JSON_ARRAYAGG function is currently not supported in the following scenarios:

• OVER windows
• Unbounded session windows
• Hop windows