Exploding and Unnesting Functions

Explode With Lists

explode() is a transformation function that unnests a list field into new rows, one per list element.

Given this table:

id	string_list
1	[a,b,c]
2	[d,e,f]

the following script:

table my_table_name is
    select
        id,
        explode(string_list) as string
from...

would result in:

id	string
1	a
1	b
1	c
2	d
2	e
2	f

Working with JSON Data

When uploading data you can declare a field to be JSON as a datatype.

download google_sheet_json_example_import from {
            type: "google_sheets";
            key: "my_google_key";
            spreadsheet_id: "{your google UIID}";   
            data_range: "A2:C4";
} using { 
    "CustomerID" : string;
    "CustomerCountry" : string;
    "CustomerInfo" : json;
};

For this example, the google spreadsheet’s field CustomerInfo has the following structure:

{
	"name": "John",
	"age": 30,
	"cars": [
		"Ford",
		"BMW",
		"Fiat"
		],
		"subobject": {
			"subobjectprop1": "valueX",
			"subobjectprop2": "valueY"
		}
}

When a field is declared as JSON, you can refer to properties and subobjects of that field as if it were a JSON object using JSON syntax within transformation tables you define.

table SwitchboardSampleJson_final is
    select
        i.CustomerID,
        i.CustomerCountry,
        i.CustomerInfo.name:string as CustomerName,
        i.CustomerInfo.age:integer as CustomerAge,
        i.CustomerInfo.cars[0]:string as CustomerInfo_firstcar,
        i.CustomerInfo.cars[1]:string as CustomerInfo_secondcar,
        i.CustomerInfo.subobject.subobjectprop1:string as CustomerInfo_subobject_subobjectprop1,
        i.CustomerInfo.subobject.subobjectprop2:string as CustomerInfo_subobject_subobjectprop2
    from google_sheet_json_example_import as i;

Using Explode with JSON Data

A more typical use case would be to create a row per item in the array.

That is we would want the data to look for like this:

CustomerID	CustomerCountry	CustomerName	CustomerAge	CustomerCar
1	Germany	John	30	Ford
1	Germany	John	30	BMW
3	Mexico	Joe	56
1	Germany	John	30	Fiat
2	Mexico	Bob	25	Tesla

In this case we would take our raw data and use the explode function on the array.

We will keep data as is except for the CustomerInfo object, from which we will extract the various properties and use the explode function on the cars array.

table SwitchboardSampleJson_explode is
    select
       <<raw.$c as "$c" for c in raw except [CustomerInfo],
       raw.CustomerInfo.name:string as CustomerName,
       raw.CustomerInfo.age:string as CustomerAge,
       raw.CustomerInfo.subobject:json as CustomerSubobject,
       explode(raw.CustomerInfo.cars) as CustomerCar
    from google_sheet_json_example_explode_import as raw unique;

Because you cannot use more than one explode per table transformation, so if you have multiple json values, you will need to add additional table transformations.

table SwitchboardSampleJson_explode2 is
    select
       <<raw.$c as "$c" for c in raw except [CustomerSubobject],
       explode(raw.CustomerSubobject) as SubobjectProperty
    from SwitchboardSampleJson_explode as raw unique;
    

The end result would be data that looks as follows:

CustomerID	CustomerCountry	CustomerName	CustomerAge	CustomerCar	SubobjectProperty
2	Mexico	Bob	25	Tesla	{“key”: “subobjectprop1”, “value”: “valueZ”}
1	Germany	John	30	BMW	{“key”: “subobjectprop1”, “value”: “valueX”}
1	Germany	John	30	Fiat	{“key”: “subobjectprop2”, “value”: “valueY”}
1	Germany	John	30	Ford	{“key”: “subobjectprop1”, “value”: “valueX”}
3	Mexico	Joe	56		{“key”: “subobjectprop2”, “value”: “valueC”}
1	Germany	John	30	BMW	{“key”: “subobjectprop2”, “value”: “valueY”}
1	Germany	John	30	Ford	{“key”: “subobjectprop2”, “value”: “valueY”}
2	Mexico	Bob	25	Tesla	{“key”: “subobjectprop2”, “value”: “valueA”}
3	Mexico	Joe	56		{“key”: “subobjectprop1”, “value”: “valueB”}
1	Germany	John	30	Fiat	{“key”: “subobjectprop1”, “value”: “valueX”}

This would apply if you had subobjects as well.

For example, if we had a custom field with a subobject.

{
	"name": "John",
	"age": 30,
	"customfield": [
		id: 123;
		value: carlist;
		],
		"subobject": {
			"subobjectprop1": "valueX",
			"subobjectprop2": "valueY"
		}
}

We first explode the customfield

table SwitchboardSampleJson_explode is
    select
       <<raw.$c as "$c" for c in raw except [customfield],
       explode(customfield) as customfield
       
    from google_sheet_json_example_import as i;

Then we can explode the custom field’s subobject.

table SwitchboardSampleJson is
    select
       <<raw.$c as "$c" for c in raw,
       customfield.id as customfieldid,
       customfield.value as customfieldvalue
       
    from SwitchboardSampleJson_explode as i;