SQL filter elements of array

Question

I have a table of employee similar to this:

Department   Data
A    [{"name":"John", "age":10, "job":"Manager"},{"name":"Eli", "age":40, "job":"Worker"},{"name":"Sam", "age":32, "job":"Manager"}]
B    [{"name":"Jack", "age":50, "job":"CEO"},{"name":"Mike", "age":334 "job":"CTO"},{"name":"Filip", "age":63, "job":"Worker"}]

I want to get the department, name, and age of all employees, something similar to this:

Department   Data
A    [{"name":"John", "age":10},{"name":"Eli", "age":40},{"name":"Sam", "age":32}]
B    [{"name":"Jack", "age":50},{"name":"Mike", "age":334},{"name":"Filip", "age":63}]

How can I achieve this using SQL query?

Vamsi Prabhala · Accepted Answer · 2020-06-24 22:03:25Z

I assume you are using Hive/Spark and the datatype of the column is an array of maps.

Using explode and collect_list and map functions.

select dept,collect_list(map("name",t.map_elem['name'],"age",t.map_elem['age'])) as res
from tbl 
lateral view explode(data) t as map_elem 
group by dept

Note that this would be not be as performant as a Spark solution or a UDF with which you can access the required keys in an array of maps, without a function like explode.

One more way to do this with Spark SQL functions transform and map_filter (only available starting Spark version 3.0.0).

spark.sql("select dept,transform(data, map_elem -> map_filter(map_elem, (k, v) -> k != \"job\")) as res from tbl")

Another option with Spark versions > 2.4 is using function element_at with transform and selecting the required keys.

spark.sql("select dept," +
          "transform(data, map_elem -> map(\"name\",element_at(map_elem,\"name\"),\"age\",element_at(map_elem,\"age\"))) as res " +
          "from tbl")

Christopher Moore · Accepted Answer · 2020-08-08 19:07:34Z

0

I'd get your table into tabular format: Department | Age | Job

Then:

SELECT Name, Age
FROM EMPLOYEE
GROUP BY Job

edited Aug 8, 2020 at 19:07

Christopher Moore

17.3k11 gold badges52 silver badges63 bronze badges

answered Jun 24, 2020 at 6:14

an.analyst

135 bronze badges

Collectives™ on Stack Overflow

SQL filter elements of array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related