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]

Mikhail Berlyant · Accepted Answer · 2022-10-03 17:37:34Z

3

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
    }
  }]
}]

answered Oct 3, 2022 at 17:37

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to construct NULL array and STRUCT in BigQuery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related