Adding column document references in Big Query

[dackers86]

Summary

Currently when saving data in Big Query(BQ), there is no way to link the row data to another document.

Based on:

#853
#707

Background

Big Query extensions are typically set using a Willard ({*}) format, this allows multiple collections/sub-collections and their associated documents to be written and cloned into a pre-defined Big Query table.

Users are currently experiencing difficulty associating those written documents with other documents that exist within the BQ project.

A reference or new data field is needed to allow SQL developers in BQ to write complex JOINS to connect document data with other documents in the same / other tables that are synchronised from Firestore.

Suggested Implementations

Solution 1: A single column reference (String)

Implementation

 export const documentIdField = {
  name: "document_parent_id",
  mode: "NULLABLE",
  type: "STRING",
  description: "The document id  of the parent document.",
};

Based on the following collection path manufacturers/vauxhall/models/corsa/colors/blue.

The result would be...

document_id: "blue",
data: {...},
document_parent_id: "corsa"

Using a SQL query, we could then use the following...

 SELECT model from MY_TABLE a
 JOIN MY_TABLE b ON a.document_id === b.document_parent_id

Issues

Reference limitations

A single reference would only allow a document to reference a single parent. If for example, this was a deeply nested sub-collection then any documents above the parent would not be able to be associated through a SQL JOIN.

Migrations

A new column will need to be added, any existing tables will not have this column under the current extension. An update table instance will be needed to add the relevant column.

Should this feature be upgraded to use structured data, can this column be converted to JSON?
Would it be a migration issue if we were to remove it?

Solution 2: A single column reference (JSON)

Implementation

Based on the following collection path manufacturers/vauxhall/models/corsa/colors/blue.

 export const documentIdField = {
  name: "document_structured_data",
  mode: "NULLABLE",
  type: "JSON",
  description: "The collection of document references.",
};

document_id: "blue",
data: {},
structured_data:
 { 
  manufacturers:[
   vauxhall: {
     models: [
      corsa: ""
     ]
  }
 }
]

Using a SQL query, we could then use the following...

 SELECT model from MY_TABLE a
 JOIN MY_TABLE b ON a.document_id === b.structured_data.manufacturers.vauxhall

Issues

JSON field types are currently experimental and would be unavailable for most users. Please see json-data

[JarnoRFB]

Thanks for taking this up! Note entirely sure about the process here, but I would like to suggest

Solution 3: A single column reference (flattened JSON as string)

Given that the extension was installed with COLLECTION_PATH=/manufacturers/{manufacturer_id}/models/{model_id}/colors

document_id: "blue",
data: {},
params: { 
  manufacturer_id: "vauxhall",
  model_id: "corsa"
}

SELECT model from MY_TABLE a
JOIN MY_TABLE b ON a.document_id === JSON_EXTRACT_SCALAR(b.params, '$.manufacturer_id')

The sql statement could be simplied when params would be spread across columns in views that build on the original table like in the schema views. In which case the query could become

SELECT model from MY_TABLE a
JOIN MY_TABLE b ON a.document_id === b.manufacturer_id

To use params as the prefix and the variable names from the wildcard patterns would fit what is currently implemented in firebase cloudfunctions that react to documents.

    functions
    .document("/manufacturers/{manufacturer_id}/models/{model_id}/colors/{color_id}")
    .onCreate(async (snap, context) => {
      console.log(context.params.manufacturer_id)
    }

I think using JSON type might be very helpful in the future, but probably a bit early since it is only in preview. With this structure one could in principle migrate from STRING to JSON in the future, while already satisfying the use case today.

[dackers86]

Thanks @JarnoRFB.

This looks like a very good solution, we will look to try and get this version implemented.