BigQuery export: Add option to include `path_params` in schema views

[jorroll]

[REQUIRED] Step 2: Extension name

This feature request is for extension: firestore-bigquery-export

• Specifically it's for the @firebaseextensions/fs-bq-schema-views script

What feature would you like to see?

I'd like for the @firebaseextensions/fs-bq-schema-views script to have an option to include the path_params in the generated schema view.

How would you use it?

Using the path_params in SQL queries. The value of this feels self evident, but let me know if it's not.

[yamankatby]

Hey @jorroll, thank you for raising this issue, I'll add it to the project tracker for further investigation.

[BenjaminKlatt]

We got a similar need and it would be great to have this option

[otopba]

Yes, this is super important.

[rahulsekar]

Workaround: The generated view query can be manually edited to get fields from 'path_params' instead of 'data'.

... FIRST_VALUE(JSON_EXTRACT_SCALAR(~~data~~ path_params, '$.classId')) OVER( PARTITION BY document_name ORDER BY timestamp DESC ) AS classId, ...

Having this in the script would be great. My suggestion for the schema json:

{ "name": "classId", "type": "string", "source": "path_params"}

[conceptualben]

This is much needed indeed. I do have a Firestore structure with sub-collections and need this for an easier join between master and child documents.

I did add my path wildcard as a field manually to the view by just like @rahulsekar mentioned pretty easily.

[luksfarris]

Seems like this feature doesn't exist yet. Would be great to have it. I'm thinking of an alternative approach, where I create another extra view that maps parent_id (parent collection) to child_id (child collection). This obviously assumes child collections have unique identifiers. Then I can join both tables to get the "path_params" id