展平BigQuery上的多个嵌套数组
展平BigQuery上的多个嵌套数组
提问于 2021-04-14 10:17:03
我在bigquery表中有几列嵌套了数组，如下所示：
marketing_table
+------+------------------+------------------+--------------------------------------------+------------------------------------+-------------------------------------+-------------+
|Row | effective_status | targeting.age_min| targeting.audience_network_positions.value | targeting.facebook_positions.value | targeting.instagram_positions.value | campaign_id |
+------+------------------+------------------+--------------------------------------------+------------------------------------+-------------------------------------+-------------+
| 1 | Active | 22 | classic | feed | stream | 1 |
| | | | instream video | video_feeds | story | |
| | | | | instant_article | explore | |
| | | | | instream_video | | |
| | | | | marketplace | | |
| | | | | story | | |
| 2 | WITH_ISSUES | 22 | classic | feed | stream | 1 |
| | | | instream video | video_feeds | story | |
| | | | | instant_article | explore | |
| | | | | instream_video | | |
| | | | | marketplace | | |
| | | | | story | | |
+------+------------------+------------------+--------------------------------------------+------------------------------------+-------------------------------------+-------------+SQL方案如下所示：
Field name, Type, Mode
-----------------------
effective_status, STRING, NULLABLE
targeting. age_min, INTEGER, NULLABLE
targeting. age_min, INTEGER, NULLABLE
targeting.audience_network_positions.value, RECORD, REPEATED
targeting. facebook_positions, RECORD, REPEATED
targeting.facebook_positions.value, STRING, NULLABLE
targeting. instagram_positions, RECORD, REPEATED
targeting.instagram_positions.value, STRING, NULLABLE
campaign_id, STRING, NULLABLE我想让它扁平化所有的嵌套数组，这样它们就会产生
marketing_table
+------+------------------+------------------+--------------------------------------------+------------------------------------+-------------------------------------+-------------+
|Row | effective_status | targeting.age_min| targeting.audience_network_positions.value | targeting.facebook_positions.value | targeting.instagram_positions.value | campaign_id |
+------+------------------+------------------+--------------------------------------------+------------------------------------+-------------------------------------+-------------+
| 1 | Active | 22 | classic | feed | stream | 1 |
| 2 | Active | 22 | instream video | video_feeds | story | 1 |
| 3 | Active | 22 | instream video | instant_article | explore | 1 |
| 4 | Active | 22 | instream video | instream_video | explore | 1 |
| 5 | Active | 22 | instream video | marketplace | explore | 1 |
| 6 | Active | 22 | instream video | story | explore | 1 |
| 7 | WITH_ISSUES | 22 | classic | feed | stream | 1 |
| 8 | WITH_ISSUES | 22 | instream video | video_feeds | story | 1 |
| 9 | WITH_ISSUES | 22 | instream video | instant_article | explore | 1 |
| 10| WITH_ISSUES | 22 | instream video | instream_video | explore | 1 |
| 11| WITH_ISSUES | 22 | instream video | marketplace | explore | 1 |
| 12| WITH_ISSUES | 22 | instream video | story | explore | 1 |
+------+------------------+------------------+--------------------------------------------+------------------------------------+-------------------------------------+-------------+你们能告诉我如何在bigquery SQL上使用unnest参数正确地取消所有这些数组的嵌套吗？
回答 1
Stack Overflow用户
发布于 2021-04-14 20:07:22
像这样的东西应该行得通。
select
t.effective_status,
t.targeting.age_min,
anp.value as anp_value,
fp.value as fp_value,
ip.value as ip_value,
t.campaign_id
from table t,
unnest(t.targeting.audience_network_positions) as anp,
unnest(t.targeting.facebook_positions) fp,
unnest(t.instagram_positions) as ip页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/67084635
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号