...
An example table with two rows:
pageid | adid_list | |
|---|---|---|
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="8bd713c3-c0d9-4657-8d53-cd9dba35dc3e"><ac:plain-text-body><![CDATA[ | front_page | [1, 2, 3] ] ]></ac:plain-text-body></ac:structured-macro><ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="84c3c5db-44e4-497c-99a5-fa9a759ec02c"><ac:plain-text-body><![CDATA[ |
contact_page | [3, 4, 5] ] ]></ac:plain-text-body></ac:structured-macro> |
and the user would like to count the total number of times an ad appears across all pages.
...
Array<int> col1 | Array<string> col2 <ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="e92c6173-faa5-4119-8022-3e6be4a47d43"><ac:plain-text-body><![CDATA[ |
[1, 2] | [a", "b", "c"] ] ]></ac:plain-text-body></ac:structured-macro><ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="b42ca26d-2e6c-4fad-bda6-879b79ddb40a"><ac:plain-text-body><![CDATA[ |
[3, 4] | [d", "e", "f"] ] ]></ac:plain-text-body></ac:structured-macro> |
The query:
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT myCol1, col2 FROM baseTable LATERAL VIEW explode(col1) myTable1 AS myCol1; |
...
int mycol1 | Array<string> col2 <ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="058ed379-22d6-402c-b104-cc7660d6bd98"><ac:plain-text-body><![CDATA[ |
1 | [a", "b", "c"] ] ]></ac:plain-text-body></ac:structured-macro><ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="ac8340ca-6cda-4f34-9e4d-03ebf2e6b266"><ac:plain-text-body><![CDATA[ |
2 | [a", "b", "c"] ] ]></ac:plain-text-body></ac:structured-macro><ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="5ce9d651-e646-49a0-9fdf-57bd25b6edf4"><ac:plain-text-body><![CDATA[ |
3 | [d", "e", "f"] ] ]></ac:plain-text-body></ac:structured-macro><ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="4be1e712-7d2f-4aeb-ae7c-566e4e8a895b"><ac:plain-text-body><![CDATA[ |
4 | [d", "e", "f"] ] ]></ac:plain-text-body></ac:structured-macro> |
A query that adds an additional LATERAL VIEW:
...