sqlapache-calcite

How to achive STRING_AGG in RelNode?

I have a query where I want to concat all the rows with a delimiter ,. I can easily achieve this in sql using STRING_AGG. How to create a relNode for the following query ?

SELECT STRING_AGG(CONCAT(col1, col2, col3), ',')
FROM table;

Is there a single SqlStdOperator which does this ?

If not, what combinations of SqlStdOperator(s) in builder.call(..., RexNode... rexNodes) to use to achieve this ?

Solution

  • Equivalent of STRING_AGG is SqlStdOperator.LISTAGG

    builder
.scan("table")
.project(
    builder.call(SqlStdOperatorTable.LISTAGG,
        builder.call(SqlStdOperatorTable.CONCAT,
            builder.field("col1"),
            builder.call(SqlStdOperatorTable.CONCAT,
                builder.field("col2"),
                builder.field("col3")
            )
        ),
        builder.literal(",")
    )
)
.build()