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.

Jeremy Condit · Accepted Answer · 2015-09-16 21:41:54Z

7

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.

answered Sep 16, 2015 at 21:41

Jeremy Condit

7,0661 gold badge30 silver badges30 bronze badges

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

8 Comments

Jegan Over a year ago

Thanks for the confirmation Jeremy.

SheRey Over a year ago

Jeremy, is this still a known issue or has it been fixed? thanks.

gwthm.in Over a year ago

Still, it is not fixed..? After 2 years ?

Anton Golubev Over a year ago

I wonder how BigQuery deals with JSON nulls in 2022...

pierresegonne Over a year ago

"I wonder how BigQuery deals with JSON nulls in 2022.." It does not

|

Collectives™ on Stack Overflow

BigQuery - Loading JSON fields with null values

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related