0

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?

noobeerp
  • 147
  • 1
  • 3
  • 9

1 Answers1

0

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'
hlagos
  • 6,261
  • 1
  • 16
  • 28
  • Does the same thing. It returns all the results for **allValues** – noobeerp Sep 26 '18 at 16:35
  • it does not make sense. can you print the select * from yourtable form hive? Also, run describe yourtable in hive and add the result. if you explode a record with 1 element array, the result should be 1 record ... – hlagos Sep 27 '18 at 07:44