[BEAM-9879] Support STRING_AGG in BeamSQL

• Type: Task
• Status: Resolved
• Priority: P3
• Resolution: Fixed
• Fix Version/s: 2.37.0
• Component/s: dsl-sql, dsl-sql-zetasql
• Assignee: Benjamin Gonzalez
• Reporter: Rui Wang
• Created: 04/May/20 18:53
• Updated: 18/Jan/22 21:42
• Resolved: 18/Jan/22 21:42

Description

Support STRING_AGG function in ZetaSQL dialect. (Also if Calcite has this function built-in, support it in Calcite dialect too)

See the reference: https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#string_agg

SELECT
       STRING_AGG(c) str_agg_unary,
       STRING_AGG(c, ", ") str_agg_binary,
       STRING_AGG(d) bytes_agg_unary,
       STRING_AGG(d, b", ") bytes_agg_binary

More test cases here: https://github.com/google/zetasql/blob/c55f967a5ae35b476437210c529691d8a73f5507/zetasql/compliance/testdata/aggregation_queries.test

Examples:

SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+------------------------+
| string_agg             |
+------------------------+
| apple,pear,banana,pear |
+------------------------+

Issue Links

• is blocked by: BEAM-11808 ZetaSQL layer doesn't support aggregate functions with two arguments
• links to: Github Pull Request #11895