Is there a way to stop nested columns taking NULL values in BigQuery when the higher-level parent column is NULL?

Question

I am trying to query a BigQuery table which has nested values. All the elements of the table are NULLABLE and the data contains NULL values. The issue is when I run the query, the nested values all take the value of either the STRING value or NULL. Instead, I would like the nested values to take the value of STRING if there is a value, otherwise the higher-level valuesField takes the value of NULL.

The database has the following schema:

{
  "name": "id",
  "type": "STRING",
  "mode": "NULLABLE"
}, 
{
  "name": "fields",
  "type": "RECORD",
  "mode": "NULLABLE",
  "fields": [{
    "name": "valuesFiled",
    "type": "RECORD",
    "mode": "NULLABLE",
    "fields": [{
      "name": "value1",
      "type": "STRING",
      "mode": "NULLABLE"
    }, {
      "name": "value2",
      "type": "STRING",
      "mode": "NULLABLE"
    }]
  }]
}

This is the query I am running:

SELECT
  id,
  STRUCT(
    CASE fields.valuesFiled
      WHEN NULL THEN NULL
      ELSE STRUCT(
        fields.valuesFiled.value1,
        fields.valuesFiled.value2
      )
      END AS values
  ) AS fields
FROM tableName;

An example of the JSON output I get:

{
  "id": "1"
  "fields": {
    "values": {
      "value1": "stringValue1",
      "value2": "stringValue2"
    }
  } 
}, {
  "id": "2"
  "fields": {
    "values": {
      "value1": null,
      "value2": null
    }
  }
}

An example of the JSON output I would like:

{
  "id": "1"
  "fields": {
    "values": {
      "value1": "stringValue1",
      "value2": "stringValue2"
    }
  } 
}, {
  "id": "2"
  "fields": {
    "values": null
  }
}

Does anyone know if this is possible with BigQuery, and if so, how it can be achieved?

Answer 1

Try this query:

SELECT
  id,
  STRUCT(
    CASE WHEN fields.valuesFiled.value1 IS NULL AND fields.valuesFiled.value2 IS NULL THEN NULL
      ELSE STRUCT(
        fields.valuesFiled.value1,
        fields.valuesFiled.value2
      )
      END AS values
  ) AS fields
FROM tableName;