How to remove one element from array after explode

Question

I have a table with column id_list in which there are a set of strings e.g. ["1","2","4","6"].

The original table is

+-------------------+
|   id_list         |
+-------------------+
| ["1","2","4","6"] |
|-------------------+
| ["1","2","4","6"] |
|-------------------+
| ["1","2","4","6"] |
|-------------------+
| ["1","2","4","6"] |
|-------------------+
| ["7"]             |
|-------------------+

I created the following table by explode the id_list column:

SELECT id, id_list FROM data LATERAL VIEW explode(id_list) dummy AS id;

+-------------+-------------------+
|     id      |   id_list         |
+-------------+-------------------+
|      1      | ["1","2","4","6"] |
+-------------|-------------------+
|      2      | ["1","2","4","6"] |
+-------------|-------------------+
|      4      | ["1","2","4","6"] |
+-------------|-------------------+
|      6      | ["1","2","4","6"] |
+-------------|-------------------+
|      7      | ["7"]             |
+-------------|-------------------+

I want to remove the id from the id_list to have a table like below:

+-------------+-------------------+
|     id      |   id_list         |
+-------------+-------------------+
|      1      | ["2","4","6"]     |
+-------------|-------------------+
|      2      | ["1","4","6"]     |
+-------------|-------------------+
|      4      | ["1","2","6"]     |
+-------------|-------------------+
|      6      | ["1","2","4"]     |
+-------------|-------------------+
|      7      | []                |
+-------------|-------------------+

How to do that without using UDF?

@leftjoin, sorry for the confusion. The id column is the exploded array from id_list, I just kept the array with the exploded elements. In other words, id "1", "2", "4", "6" are from the exploded array. — geowav
– geowav, Commented Aug 9, 2019 at 13:15

leftjoin · Accepted Answer · 2019-08-09 14:02:26Z

4

You can explode and collect only those elements which are not equal id.

Demo:

with initial_data as (
select 1 id   ,array("1","2","4","6") list union all
select 2   ,array("1","2","4","6") list union all    
select 3   ,array("1","2","4","6")   list union all 
select 4  ,array("1","2","4","6")   list union all 
select 6   ,array("1","2","4","6")   list union all
select 7   ,array("7")  
)    


SELECT d.id, collect_list(case when e.id!= d.id then e.id end) id_list
  FROM initial_data d 
       LATERAL VIEW explode(list) e AS id
 GROUP BY d.id;

Result:

OK
id      id_list
1       ["2","4","6"]
2       ["1","4","6"]
3       ["1","2","4","6"]
4       ["1","2","6"]
6       ["1","2","4"]
7       []
Time taken: 38.645 seconds, Fetched: 6 row(s)

edited Aug 9, 2019 at 14:02

answered Aug 9, 2019 at 5:50

leftjoin

38.5k8 gold badges64 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

geowav Over a year ago

Thanks@leftjoin. I have modified the original question.

geowav Over a year ago

I think you answered my question. I just need to explode one more time. By the way, I think GROUP BY d.id should be appended in the original query.

Collectives™ on Stack Overflow

How to remove one element from array after explode

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related