4

Suppose we have

columnsA        columnsB
   1           {'A':22,'B':11}
   2           {'A':13,'C':24}

How to convert it to

columnsA   A   B   C
   1       22  11  NA
   2       13  NA  24
Ajith
  • 1,447
  • 2
  • 17
  • 31
takashi
  • 41
  • 2

1 Answers1

0

Use Map[key] operator

select m['A'] A,  
       m['B'] B, 
       m['C'] C 
  from (select str_to_map("A:22,B:11") m) s;

Returns:

A       B       C
22      11      NULL

If you need 'NA' instead of NULL, use NVL() function:

NVL(m['A'],'NA') as A
leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • thank you so much! And there are more than 200 discrete values in columnB. Is there any other way? – takashi Dec 20 '19 at 01:23
  • @takashi the other way can be using `lateral view outer explode(mapCol) s as key, val;` like in this answer: https://stackoverflow.com/a/54960324/2700344 But after that you additionally need to transpose (key, val) to the columns based on keys, this can be done using conditional aggregation. Using map[key] would be much simpler and efficient – leftjoin Dec 20 '19 at 04:42