从配置单元ql中的数组获取正确的值

kuuvgm7e  于 2021-06-26  发布在  Hive
关注(0)|答案(1)|浏览(339)

我有一个 Package 数组,当我用横向视图explode查询时,只想得到相应的值结构。
样本结构:
列名:thearray

WrappedArray([null,theVal,valTags,[123,null,null,null,null,null],false], [null,theVar,varTags,[abc,null,null,null,null,null],false])

架构是

array<struct<id:string,name:string,type:string,value:struct<member0:string,member1:bigint,member2:int,member3:double,member4:float,member5:boolean>,shouldIndex:boolean>>

我的问题:

SELECT DISTINCT theName, allValues
FROM table 
LATERAL VIEW EXPLODE(column.name) theTab1 AS theName
LATERAL VIEW EXPLODE(column.value.member0) theTab2 AS allValues
WHERE theName = 'theVal'

我的结果:

___________________________
|**theName**|**allValues**|
___________________________
|theVal     |     123     |
___________________________
| theVal    |     abc     |
___________________________

我需要:

___________________________
|**theName**|**allValues**|
___________________________
|theVal     |     123     |
___________________________

如何修复我的查询以获得上述结果?

eeq64g8w

eeq64g8w1#

不需要在结构顶部进行额外的爆炸。你应该可以这样做

SELECT DISTINCT theName, column.value.member0
FROM table 
LATERAL VIEW EXPLODE(column.name) theTab1 AS theName
WHERE theName = 'theVal'

相关问题