How to convert array<string> to string in hive

0
votes

I want to convert an array<string> to string in hive. The array data is as follow:

+-------------------------------------+--+
| NULL                                |
| ["Extension","Terms & Conditions"]  |
| ["Value (generic or item level)"]   |
+-------------------------------------+--+

I want to collect array values to convert to string without [""] so that I could get result like:

+-------------------------------------+--+
| NULL                                |
| Extension,Terms & Conditions        |
| Value (generic or item level)       |
+-------------------------------------+--+

Following query: select concat_ws(',', col_name) as col_name from table_stg; provides the result but is is returning NULL as empty. I tried several reference like:

How can I convert array to string in hive sql?

Hive - How to cast array to string?

But not getting the desired result. Is there any way to get the desired result?

1
arraysstringhivehiveql
use a case expression. case when size(col_name) = 0 then null else concat_ws(',',col_name) end – Vamsi Prabhala
Hi @VamsiPrabhala - checking size doesn't worked, but it gave me the idea to solve this. thanks for your comment :) – Sandeep Singh

1 Answers

0
votes

With reference to Vamsi comment, I managed to get this and thought to answer as well for the community reference.

select case when col_name is NULL then NULL 
    else concat_ws(',',col_name) end from table_name;