How do you set the field names in a struct type declaration when the value is NULL?

Question

I'm building the union of two tables with different columns. To make them match, I set the missing values to NULL and declare the type:

WITH
  table1 AS (
  SELECT
    STRUCT(1 AS b,
      2 AS c) AS a,
    CAST(NULL AS STRUCT<STRING, STRING>) AS x),
  table2 AS (
  SELECT
    CAST(NULL AS STRUCT<INT64, INT64>) AS a,
    STRUCT('hello' AS y,
      'world' AS z) AS x)
SELECT
  *
FROM
  table1
UNION ALL
SELECT
  *
FROM
  table2

The types work out, but in the output, only the struct fields that were declared in the first table get the intended name:

a x
"{
  ""a"": {
    ""b"": ""1"",
    ""c"": ""2""
  }
}"  "{
  ""x"": null
}"
"{
  ""a"": null
}"  "{
  ""x"": {
    ""_field_1"": ""hello"",
    ""_field_2"": ""world""
  }
}"

I.e., x.y and x.z come out as x._field_1 and x._field_2.

Is there a way to fix that, i.e., define the names of the struct field with the type declaration of x in table1?

Answer 1

Oh, found the solution. It's surprisingly simple. :D

WITH
  table1 AS (
  SELECT
    STRUCT(1 AS b,
      2 AS c) AS a,
    CAST(NULL AS STRUCT<y STRING, z STRING>) AS x),
  table2 AS (
  SELECT
    CAST(NULL AS STRUCT<b INT64, c INT64>) AS a,
    STRUCT('hello' AS y,
      'world' AS z) AS x)
SELECT
  *
FROM
  table1
UNION ALL
SELECT
  *
FROM
  table2

Output:

"{
  ""a"": {
    ""b"": ""1"",
    ""c"": ""2""
  }
}"  "{
  ""x"": null
}"
"{
  ""a"": null
}"  "{
  ""x"": {
    ""y"": ""hello"",
    ""z"": ""world""
  }
}"