groupFormat

Formats rows in each group using an output format and returns the formatted data as a string.

Formats rows in each group using an output format and returns the formatted data as a string. This is similar to formatRow, but works on the whole group and can use block-based formats.

All rows of each group are accumulated in memory before the formatted string is produced. For groups with a very large number of rows this can consume significant memory. Consider using LIMIT inside subqueries or splitting large groups to keep memory usage under control.

Syntax

groupFormat(format)(x, y, ...)

Parameters

• format — Output format name, for example JSONEachRow, CSV, TabSeparated.

Arguments

• x, y, ... — Expressions to format as rows. At least one argument is required.

Returned value

• A String containing the formatted output for the group.

Column names in the formatted output are generated as c1, c2, … in the order of arguments.

The particular order of formatted rows is not guaranteed.

The query’s format settings (for example format_csv_delimiter or output_format_json_quote_64bit_integers) are captured when the aggregate function is initialized and used to produce the output. The output_format_write_statistics setting is always forced off, so the formatted string never contains a statistics section.

NULL handling

Like groupArray and groupConcat, groupFormat skips a row when any of its arguments is NULL; such rows do not appear in the formatted output. When an argument is nullable, the result type is Nullable(String), and a group whose every row is skipped — as well as a literal untyped NULL argument of type Nullable(Nothing) — returns NULL through the generic Null combinator.

SELECT groupFormat('JSONEachRow')(if(number = 0, NULL, number))
FROM numbers(3);
-- {"c1":1}
-- {"c1":2}

Examples

Basic usage with JSONEachRow

SELECT groupFormat('JSONEachRow')(number, toString(number))
FROM numbers(3);

Result:

{"c1":0,"c2":"0"}
{"c1":1,"c2":"1"}
{"c1":2,"c2":"2"}

groupFormat

Introduced in: v

Formats the rows in each group using the specified output format and returns the result as a string.

The format name is passed as a parameter, and the arguments are the columns to format. Column names are generated as c1, c2, … in the formatted output.

Syntax

groupFormat(format)(x, y, ...)

Parameters

• format — Output format name. For example, JSONEachRow, CSV, TabSeparated. String

Arguments

• x, y, ... — Expressions to format as rows. Any

Returned value

Formatted output for the group. String

Examples

Basic usage

Query

SELECT groupFormat('JSONEachRow')(number, toString(number))
FROM numbers(3)

Response

{"c1":0,"c2":"0"}
{"c1":1,"c2":"1"}
{"c1":2,"c2":"2"}