The FLATTEN directive separates the elements in a repeated field into individual records.
Syntax
Code Block |
---|
flatten :column[,:column]* |
The column
is the name of a column that is a JSON array.
Usage Notes
The FLATTEN directive is useful for the flexible exploration of repeated data.
To maintain the association between each flattened value and the other fields in the record, the FLATTEN directive copies all of the other columns into each new record.
Examples
Case 1
The array in col2
is flattened and the values in col3
are repeated for each value of col2
:
...
Code Block |
---|
[ { "col1": "A" }, { "col1": "B" }, { "col2": "x1", "col3": 10 }, { "col2": "y1", "col3": 10 }, { "col2": "x2", "col3": 11 }, { "col2": "y2", "col3": 11 } ] |
Case 2
The arrays in col2
and col3
are flattened:
...
Code Block |
---|
[ { "col1": "A" }, { "col1": "B" }, { "col2": "x1", "col3": "a1" }, { "col2": "y1", "col3": "b1" }, { "col2": "z1", "col3": "c1" }, { "col2": "x2", "col3": "a2" }, { "col2": "y2", "col3": "b2" } ] |
Case 3
The arrays in col2
and col3
are flattened:
...
Code Block |
---|
[ { "col1": "A" }, { "col1": "B" }, { "col2": "x1", "col3": "a1" }, { "col2": "y1", "col3": "b1" }, { "col2": "z1" }, { "col2": "x2", "col3": "a2" }, { "col2": "y2", "col3": "b2" }, { "col3": "c2" } ] |
Case 4
Using this record as an example:
...