Skip to main content
Skip to main content
Edit this page

groupFormat

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.

Note

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.
Note

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"}