Expand

EXPAND unwinds a nested value into rows. Given a field whose value is an array, a map, or an array of maps, it produces one row per element and flattens the nested structure into your result set. It works on a single field at a time; to unwind more than one field in the same step, use EXPAND_ARRAYS or EXPAND_ARRAYS_WITH_DEFAULTS.
 

select EXPAND(<Column with Nested Value>)

Example: Here the Name field holds an array of maps, each with a Last Name and First Name.

Note that this function must be used in isolation, i.e., cannot be used in combination with others. Use query chaining to manipulate the results:

select expand(Name);

select * where First Name like John;

EXPAND_ARRAYS

Unwinds multiple nested field values: This function can expand multiple arrays, maps, or arrays of maps data structures into rows.

select EXPAND_ARRAYS(<Column with Nested Value 1>, ..., <Column with Nested Value N>)

Example: In this example, there are two nested objects Grade and Address. Grade field’s value is an array of map of three fields date, grade, and score. Address field’s value is a map of four fields building, coord, street, zipcode.

Note that this function must be used in isolation, i.e., cannot be used in combination with others. Use query chaining to manipulate the results:

select nestedObj1 as Nested1, nestedObj2.secondLevel.y as Nested2;

select expand_arrays(Nested1, Nested2);

 Nested fields before unwinding with EXPAND_ARRAYS() function:

To unwind/expand multiple arrays, use the expand_arrays syntax.

Example:

select nestedObj1 as Nested1, nestedObj2.secondLevel.y as Nested2;
select expand_arrays(Nested1, Nested2);

Note that expand_arrays must be specified on its own, without any other elements within the select.

EXPAND_ARRAYS_WITH_DEFAULTS

A more powerful version of EXPAND_ARRAYS. This function can expand multiple arrays, maps, or arrays of maps data structures into rows. It also allows you to fill in blank fields of expanded arrays with a default value like nulls or a chosen value if the arrays are different in size.

EXPAND_ARRAYS_WITH_DEFAULTS(<field 1>, 0, <field 2>, now(), <field 3>, LAST, <field 4>, <value from another field on the same row>, ..., <field N>, NULL)

Example: In this example, there are two nested objects Grade and Address. Grade field’s value is an array of map of three fields date, grade, and score. Address field’s value is a map of four fields building, coord, street, zipcode.

When you unwind grade and coord with EXPAND_ARRAYS(), you find that grade has more rows than coord.

With this function, you can choose a default value to fill in the blank spaces. This value will follow that object in the function. Below, we chose to fill in the blank spaces for the coord field with 0.

Note that a default value is required for all fields. If there is no particular default value you wish to add, simply enter null after the field as seen below for the field grade.

select grade, address.coord as coord;
select expand_arrays_with_defaults(grade, null, coord, 0);

The Result:

To unwind/expand multiple arrays and fill in values in the function, use the expand_arrays_with_defaults syntax.

Example:

select nestedObj1 as Nested1, nestedObj2.secondLevel.y as Nested2;
select expand_arrays_with_defaults(Nested1,null, Nested2,0);

Note that expand_arrays_with_defaults must be specified on its own, without any other elements within the select.

EXPAND_PATH

Works like EXPAND, unwinding a nested array, map, or array of maps into rows, but it keeps the full path of each nested field as the resulting column name (prefixing child fields with their parent path). This avoids name collisions when different parents contain fields with the same name.

select EXPAND_PATH(<Column with Nested Value>)

Note that, like EXPAND, this function must be specified on its own, without any other elements within the select. Use query chaining to manipulate the results.