How can I convert array to string in hive sql?

Question

I want to convert an array to string in hive. I want to collect_set array values to convert to string without [[""]].

select actor, collect_set(date) as grpdate from actor_table group by actor; 

so that [["2016-07-01", "2016-07-02"]] would become 2016-07-01, 2016-07-02

Answer 1

Use concat_ws(string delimiter, array<string>) function to concatenate array:

select actor, concat_ws(',',collect_set(date)) as grpdate from actor_table group by actor; 

If the date field is not string, then convert it to string:

concat_ws(',',collect_set(cast(date as string))) 

Read also this answer about alternative ways if you already have an array (of int) and do not want to explode it to convert element type to string: How to concatenate the elements of int array to string in Hive

Answer 2

Sometimes, you may need a JSON formatted list, so you can simply use:

SELECT CAST(COLLECT_SET(date) AS STRING) AS dates FROM actor_table  

PS: I needed this but found only your question about array to string conversion.