Is it possible to dynamically add fields to a nested column of type RECORD in BiqQuery without having to change the entire schema?

Question

Say I have a schema that looks like this for example:

[
    {
        "name": "store",
        "type": "GEOGRAPHY",
        "mode": "REQUIRED"
    },
    {
        "name": "customer",
        "type": "RECORD",
        "mode": "NULLABLE",
        "fields": [
            {
                "name": "balance",
                "type": "INTEGER",
                "mode": "NULLABLE"
            }
     }
]

Now I want to add a row of data that contains another field for the customer record that the other rows in the database didn't have yet, for example "debt". Is it possible to have different rows have different fields from each other for the nested record without having to update the general schema? And if not, does BigQuery offer tools to dynamically update the entire schema based on addition to the database if there are new entries?

Answer 1

You have a few options of what you can do if you have some control of how the data goes into BigQuery, and one if you do not.

1. The JSON data type, currently in preview, would allow you to store the type as JSON and then define the schema on read for specific elements in the JSON object.
2. Use a JSON formatted string and JSON functions, similarly to the above, but this is an already GA feature.
3. If you cannot control the data being input then you could do what @Mr.Batra suggested and use the auto-detect schema functionality.