JSON_ARRAYAGG

Applies toBYOC

1 min read

On this page

Features
Supported Version
Syntax
Input Parameters
Examples
- 1. Create a Source Table
- 2. Aggregate Items into a JSON Array
Limitations

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

SQL

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

Input Parameters

Parameter	Data type	Description
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

SQL

1CREATE TEMPORARY TABLE src (
2  n INT
3) WITH (
4  'connector' = 'datagen',
5  'number-of-rows' = '5',
6  'fields.n.kind' = 'sequence',
7  'fields.n.start' = '1',
8  'fields.n.end' = '5'
9);

2. Aggregate Items into a JSON Array

SQL

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

Output Result

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

JSON

1["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

Was this helpful?

Yes No