We have a significant savings in the number of bytes scanned. format(query_size_check(query)))Īs we can see again these results match our struct table findings. # How many bytes will this query process? Json_extract_scalar(b,'$.State') as state_name,Ĭast(json_extract_scalar(b,'$.Population') as int64) as state_populationįrom us_state_populations a, unnest(json_extract_array(a.us_state_population_json,'$.data')) as b Json_extract_scalar(b,'$.ID State') as state_id, Our example source JSON data is a list of United States state populations from the datausa.io API. If not please check out our other post Google BigQuery and Colab - Getting Started and come back when ready. This post assumes you are already familiar with BigQuery and Colab. Transform the JSON into a standard table with columnsĪdditionally, we will make use of the recently released json_extract_array function that makes it much easier to work with arrays nested from our source JSON. May be new territory for SQL users who are not used to using nested fields Every query will pay the cost of hitting the entire JSON object.ĭepending on your use case this may better fit your data needs, especially if you have nested arrays in your JSON
We lose BigQuery’s columnar data storage benefits. JSON string column with BigQuery JSON functionsĮasiest to use directly from the source system
Bigquery json extract example how to#
In BigQuery we have a few options to consider when choosing how to store this data for use in BigQuery. JSON allows for a flexible schema that supports nested value pairs and arrays. A common way of logging and delivering data from production systems is via the JSON format.
0 kommentar(er)
