Use the dot notation to query nested elements and the array notation for selecting items in an array. The example below uses a JSON nested string and uses Cloud9QL to parse it.
EXPAND
Unwinds nested field values: This function will expand an array, map, or array of a maps data structure into rows. This only works for a single field. To unwind multiple fields, see EXPAND_ARRAYS or EXPAND_ARRAYS_WITH_DEFAULTS
select EXPAND(<Column with Nested Value>)
Example: In this example, Name field's value is an array of map of 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 customer, nestedObj.secondLevel.y as Nested;
select expand(Nested);
To unwind/expand an array, use the expand syntax.
Example:
select customer, nestedObj.secondLevel.y as Nested;
select expand(Nested);
Note that expand must be specified on its own, without any other elements within the select.
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);
Nested fields after unwinding with EXPAND_ARRAYS() function:
Note that expand_arrays must be specified on its own, without any other elements within the select.
EXPAND_ARRAYS_WITH_DEFAULT
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 the grade and coord with EXPAND_ARRAYS(), you find that grade has more rows than the 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 a 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.