firestore-bigquery-export schema type record for data?

[dfischer]

[REQUIRED] Step 2: Extension name

firestore-bigquery-export

What feature would you like to see?

When browsing the records I noticed they return the payload in a catchall data field. If I want to query on this field as if it were parsed into a record from json what is the best practice here?

How would you use it?

Use big query more effectively on collection attributes.

[rphlmr]

It would be nice to store only data field :)

For now, I use this query to work with my datas :

SELECT
  JSON_VALUE(data,'$.transactionId') AS transactionId,
  JSON_VALUE(data,'$.createdAt') AS createdAt,
  JSON_VALUE(data,'$.sellerId') AS sellerId,
  JSON_VALUE(data,'$.customerId') AS customerId,
  JSON_VALUE(data,'$.saleAmount') AS saleAmount,
  JSON_VALUE(data,'$.offerId') AS offerId,
FROM
  `myproject.firestore_exports.transactions_raw_changelog`

You can store this result in a new dataset but it will produce a lot of duplicated datas (except if you erase all data when this query is run).

Here, we call this query everytime we want to refresh our data studio dashboard (I don't know if we could do something better for the moment)

[rphlmr]

Maybe could be merged with #113 :)
I'll try soon this suggestion :D

[jhuleatt]

There is now a script for this 😃

https://github.com/firebase/extensions/blob/next/firestore-bigquery-export/guides/GENERATE_SCHEMA_VIEWS.md