BigQuery - Loading JSON fields with null values

Question

I am trying to load json files into Bigquery using the java client library. Some of the Json fields are populated as null. And the load fails with the below error from Bigquery.

{
    "reason": "invalid",
    "location": "File: 0 / Offset:0 / Line:1 / Column:1666 / Field:field1",
    "message": "flat value specified for record field"
}

My schema is this.

{
    "name": "field1",
    "type": "RECORD",
    "mode": "NULLABLE",
    "fields": [{
        "name": "field2",
        "type": "TIMESTAMP",
        "mode": "REQUIRED"
    },
    {
        "name": "field3",
        "type": "TIMESTAMP",
        "mode": "REQUIRED"
    }]
}

And my Json is like this.

{
    ...
    "field1": null,
    ...
}

I saw these 2 links.

Import json data with null values and Loading nulls into bigquery with bq

Unfortunately, I cannot change the json to remove the fields with null values. Is there a way to load such json in Bigquery? I could not find much information in the documentation. Please point me to some documentation or some work-arounds. Thanks for your time.

Answer 1

This is a known issue with our current JSON imports. Unfortunately the only way to load a null record is to omit the record from the JSON completely, rather that setting it to null explicitly. I'll bump the priority on the bug internally, but there may not be an immediate fix.