How to construct NULL array and STRUCT in BigQuery

Question

I have a table that has an array field. Here is the DDL on a temp table I made with just the field in question:

CREATE TABLE `project.dataset.table`
(
  field ARRAY<STRUCT<value STRUCT<float_val FLOAT64, int_val INT64, string_val STRING>>>
)

How would I explicitly insert a NULL value for the field?

I tried:

SELECT
  ARRAY(
    SELECT AS STRUCT(
      SELECT AS STRUCT(
        CAST(NULL AS FLOAT64) AS float_val,
        CAST(NULL AS INTEGER) AS int_val,
        CAST(NULL AS STRING) AS string_val
      )
    )
  AS field)

But I get the error: Syntax error: Parenthesized expression cannot be parsed as an expression, struct constructor, or subquery at [5:9]

Answer 1

Try below

SELECT
  ARRAY(
    SELECT AS STRUCT(
      SELECT AS STRUCT 
        CAST(NULL AS FLOAT64) AS float_val,
        CAST(NULL AS INTEGER) AS int_val,
        CAST(NULL AS STRING) AS string_val
    ) as value)
  AS field           

which produces below schema

[{
  "field": [{
    "value": {
      "float_val": null,
      "int_val": null,
      "string_val": null
    }
  }]
}]