Build Nested structure using BeamSQL

Question

In BigQuery we have "ARRAY_AGG" function which helps to convert the normal collection to Nested collection. Is there a similar way to build same kind of nested structure collection using BeamSQL?. Something like below query in BeamSQL,

"Select column1, ARRAY_AGG(STRUCT(column2, column3)) from PCOLLECTION Group by Column1"

Answer 1

If I understood your question correctly, you should be able to use ARRAY constructor like "SELECT ARRAY[1, 2, 3] f_arr", this passes:

  @Test
  public void testArrayConstructor() {
    BeamSqlEnv sqlEnv = BeamSqlEnv.inMemory(readOnlyTableProvider);
    PCollection<Row> stream =
        BeamSqlRelUtils.toPCollection(pipeline, sqlEnv.parseQuery("SELECT ARRAY[1, 2, 3] f_arr"));
    PAssert.that(stream)
        .containsInAnyOrder(
            Row.withSchema(Schema.builder().addArrayField("f_arr", FieldType.INT32).build())
                .addValue(Arrays.asList(1, 2, 3))
                .build());
    pipeline.run().waitUntilFinish(Duration.standardMinutes(2));
  }

See also:

• Calcite SQL reference guide (it is the parser used by Beam SQL);
• Beam complex types test;
• Beam nested rows test