3

I'm currently using the Export Collections to BigQuery Firebase Extension.

This is a Firebase Function that periodically updates Firestore Collections to BigQuery.

This is great, but it seems to put Firestore Document Data into a "data" column inside BigQuery.

My question, how would I go about getting this JSON out of the data column into separate columns in BigQuery. Luckily my JSON/Firestore documents are not nested and are flat and I intend to keep it that way.

Any advice would be great. I'm aware of https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions but am struggling to find the right SQL query to achieve this.

Trevor Lazarus
  • 139
  • 8

2 Answers2

3
    SELECT 
      JSON_EXTRACT(data, "$.user") AS user
    FROM `firebase-project.firestore_export.name-of-firestore-collection`

is what I able to use to pull JSON from the data column and format it into different columns.

Thanks rtenha!

Trevor Lazarus
  • 139
  • 8
2

You should be able to use JSON_EXTRACT_SCALAR to "column-ize" your data.

with data as (select '{ "name" : "Jakob", "age" : "6" }' as my_json)
select 
  JSON_EXTRACT_SCALAR(my_json,'$.name') as name,
  JSON_EXTRACT_SCALAR(my_json,'$.age') as age
from data

Consider keeping your firebase 'source' data as is, then create a view that parses the json to give you useable columns.

rtenha
  • 3,349
  • 1
  • 6
  • 19
  • Thanks for your response rtenha. The Firebase Extension has given us the query: ` SELECT data FROM `firebase-project-id.firestore_export.firestore_collection_name_raw_latest ` This gives me the data column with all the properties in my documents – Trevor Lazarus Nov 26 '19 at 12:11