Nested rows using STRUCT are not supported in Dataflow SQL (GCP)

Question

With Dataflow SQL I would like to read a Pub/Sub topic, enrich the message and write the message to a Pub/Sub topic.

Which Dataflow SQL query will create my desired output message?

Pub/Sub input message: {"event_timestamp":1619784049000, "device":{"ID":"some_id"}}

Desired Pub/Sub output message: {"event_timestamp":1619784049000, "device":{“ID":"some_id", “NAME”:”some_name”}}

What I get is: {"event_timestamp":1619784049000, "device":{"ID":"some_id"}, "NAME":"some_name" }

but I need the NAME inside the “device” attribute.

SELECT message_table.device as device, devices.name as NAME 
FROM pubsub.topic.project_id.`topic` as message_table
  JOIN bigquery.table.project_id.dataflow_sql_dataset.devices as devices 
  ON devices.device_id = message_table.device.id

Answer 1

Unfortunately, Dataflow SQL does not currently support STRUCT/Sub queries, but we are working on it. Since there are some Apache Beam dependencies preventing its progress (Nested Rows Support, Upgrading Calcite), we cannot provide an ETA at the moment, but you can follow its progress on this issue tracker.

Answer 2

You need to create a struct in the projection (SELECT part)

SELECT STRUCT(message_table.device.ID as ID , devices.name as NAME) as device
FROM pubsub.topic.project_id.`topic` as message_table
  JOIN bigquery.table.project_id.dataflow_sql_dataset.devices as devices 
  ON devices.device_id = message_table.device.id