null values in export data bigquery

[larisa]

I'm trying to export tables from BigQuery into a bucket in google cloud storage in JSON format. it seems that the export data removes the fields with null values from the JSON files. is there any way available to handle the null values while exporting data

[SidVjSingh]

Hey 👋, I'm also working on a similar project for myself, can you tell me how to upload the data in json format as you have done till now. If there's any resource for this please share, that would be helpful. Thanks 😊 

[larisa]

https://cloud.google.com/bigquery/docs/reference/standard-sql/other-statements#export_data_statement i'm using export data statement

[Roderick]

Yes, there is a way to handle null values while exporting data from BigQuery to Google Cloud Storage in JSON format. You can use the nullValuesAs parameter to specify how null values should be handled. The following options are available:

• IGNORE: Null values will be ignored and not exported.
• NULL: Null values will be exported as empty strings.
• ``"":` Null values will be exported as the empty string enclosed in double quotes.

For example, the following command will export the table my_table to the bucket my-bucket in JSON format, and null values will be exported as empty strings:

bq mk --table my_table --outputFormat JSON --destination_bucket my-bucket --nullValuesAs NULL

You can also use the nullValuesAs parameter to specify a different value for each field. For example, the following command will export the table my_table to the bucket my-bucket in JSON format, and null values for the field1 field will be exported as the empty string, while null values for the field2 field will be exported as the empty string enclosed in double quotes:

bq mk --table my_table --outputFormat JSON --destination_bucket my-bucket --nullValuesAs field1:NULL,field2:""`

[Felix5]

It is not clear to me, how

bq mk ---table 

could possibly solve the raised problem. The issue is not to create a table, but to transfer data to Cloud Storage.

Furthermore, several flags mentioned in this post do not exist and it looks like they have never existed in previous versions.

Could you please follow-up on if there is a solution to the raised issue?

[Roderick]

It appears the initial solution was unsuccessful for several community members. As a temporary workaround, Stack Overflow users suggest utilizing TO_JSON_STRING(NULL), as demonstrated in this thread: [link to Stack Overflow thread]

Alternatively, some have found success by extracting data as a Parquet file and subsequently converting it to JSONL format.

[MR2]

Which version of bq mk allows above syntax? The latest v2.0.98 doesn't have most of the flags mentioned, including --nullValuesAs