0

I have a Wrapped Array and want to only get the Non Null values when I query with LATERAL VIEW EXPLODE. I also tried IS NOT NULL but that does not return anything.

SAMPLE STRUCTURE:

COLUMNNAME: theARRAY

WrappedArray([null,theVal,tags,[null,null,7298,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 revs
FROM events 
LATERAL VIEW EXPLODE(theARRAY.value.member2) theTab2 AS revs

My result: 

__________
|**revs**|
__________
|7298    |
__________
| null   |
__________

WHen is use the IS NOT NULL: 

SELECT DISTINCT revs
FROM events 
LATERAL VIEW EXPLODE(theARRAY.value.member2) theTab2 AS revs 
revs IS NOT NULL

nothing gets returned.

I need: 

|**revs**|
__________
|7298    |
__________

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

Ramesh Maharjan
  • 41,071
  • 6
  • 69
  • 97
noobeerp
  • 417
  • 2
  • 6
  • 11

1 Answers1

1

You can refine the query as:

SELECT revs FROM
( SELECT DISTINCT revs 'revs' 
FROM events 
LATERAL VIEW EXPLODE(theARRAY.value.member2) theTab2 AS revs 
) WHERE revs IS NOT NULL
Rahul
  • 399
  • 4
  • 10