Firebase - Unable to join collections using document_id using BigQuery

Question

I'm using Firestore and have installed the BigQuery extension. I'm having trouble joining two tables/collections using the document_id? This is working as expected if I use other columns for the join, i'm just having trouble using the document_id.

SELECT 
    JSON_EXTRACT(foo.data, '$.name') AS user_name,
    JSON_EXTRACT(bar.data, '$.name') AS company_name
FROM `foo_table` AS foo
LEFT JOIN `bar_table` AS bar
ON CAST(JSON_EXTRACT(foo.data, '$.bar') AS STRING) = bar.document_id;

Answer 1

Use JSON_VALUE() instead of JSON_EXTRACT() to remove extra quotes and match the document_id correctly:

SELECT 
    JSON_VALUE(foo.data, '$.name') AS user_name,
    JSON_VALUE(bar.data, '$.name') AS company_name
FROM `foo_table` AS foo
LEFT JOIN `bar_table` AS bar
ON JSON_VALUE(foo.data, '$.bar') = bar.document_id;

This ensures the extracted value is a plain string, preventing mismatches.