Get correct value from array in Hive QL

Question

I have a Wrapped Array and want to only get the corresponding value struct when I query with LATERAL VIEW EXPLODE.

SAMPLE STRUCTURE:

COLUMNNAME: theARRAY

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

schema is

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

My Query:

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'

My result:

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

I need:

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

How can fix my query to get the result as above?

Answer 1

Your additional explode on top of the structure is not needed. You should be able to perform that like this

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