how to convert string into complex array of struct and explode in hive

Question

I have below hive table

id     string 
code   string
config string  

values:

dummyID|codeA|[{"pmc":"111","scc":"aa1","pgtp":"a22","pgn":"a33","pgrc":"a44"},{"pmc":"222","scc":"bb1","pgtp":"b22","pgn":"b33","pgrc":"b44","sen":"b77"},{"pmc":"333","scc":"cc1","pgtp":"c22","pgn":"c33","pgrc":"c44","pscc":[],"mapb":"c88"},{"pmc":"444","scc":"dd1","pgtp":"d22","pgn":"d33","pgrc":"d44","pscc":["ghgh"],"mapb":"d88"},{"pmc":"555","scc":"ee1","pgtp":"e22","pgn":"e33","pgrc":"e44","mapb":"e88"}]

I need to explode the array like below output : ( any of the elements under struct can be optional)

dummyID|codeA|{"pmc":"111","scc":"aa1","pgtp":"a22","pgn":"a33","pgrc":"a44"}
dummyID|codeA|{"pmc":"222","scc":"bb1","pgtp":"b22","pgn":"b33","pgrc":"b44","sen":"b77"}
dummyID|codeA|{"pmc":"333","scc":"cc1","pgtp":"c22","pgn":"c33","pgrc":"c44","pscc":[{"qtgm":"tt1","swrt":"rr2"}],"mapb":"c88"}
dummyID|codeA|{"pmc":"444","scc":"dd1","pgtp":"d22","pgn":"d33","pgrc":"d44","pscc":["ghgh"],"mapb":"d88"}
dummyID|codeA|{"pmc":"555","scc":"ee1","pgtp":"e22","pgn":"e33","pgrc":"e44","mapb":"e88"}

I tried:

select 
id,
code,
exp_val   
FROM   temp 
LATERAL VIEW explode(array(config)) temp AS exp_val ;

above query is not giving any error but not exploding and getting single row, lateral view inline didn't work too

I tried to create table with below schema and tried to insert records from above string config field but it failed with data type mismatch error

id    string,
code  string,
config  array<struct<pmc:String,scc:String,pgtp:string,pgn:string,pgrc:string,pscc:Array<String>,sen:Array<String>,mapb:Array<String>>> 

when I tried to run select query for config i got below result

|dummyID|codeA|{"pmc":"[{\"pmc\":\"111\",\"scc\":\"aa1\",\"pgtp\":\"a22\",\"pgn\":\"a33\",\"pgrc\":\"a44\"},{\"pmc\":\"222\",\"scc\":\"bb1\",\"pgtp\":\"b22\",\"pgn\":\"b33\",\"pgrc\":\"b44\",\"sen\":\"b77\"},{\"pmc\":\"333\",\"scc\":\"cc1\",\"pgtp\":\"c22\",\"pgn\":\"c33\",\"pgrc\":\"c44\",\"pscc\":[],\"mapb\":\"c88\"},{\"pmc\":\"444\",\"scc\":\"dd1\",\"pgtp\":\"d22\",\"pgn\":\"d33\",\"pgrc\":\"d44\",\"pscc\":[\"ghgh\"],\"mapb\":\"d88\"},{\"pmc\":\"555\",\"scc\":\"ee1\",\"pgtp\":\"e22\",\"pgn\":\"e33\",\"pgrc\":\"e44\",\"mapb\":\"e88\"}]","scc":null,"pgtp":null,"pgn":null,"pgrc":null,"pscc":null,"sen":null,"mapb":null} 

Explode did'nt work on this dataset too

Is there anything I am missing?

Answer 1

remove array inside explode function and try the following

select 
 id,
 code,
 exp_val   
FROM temp 
LATERAL VIEW explode(config) temp AS exp_val ;

Second Option:

select 
 t.id,
 t.code,
 e.*   
FROM temp t
LATERAL VIEW outer inline(t.config) e ;

Answer 2

You can try this approach,

First SPLIT the String to transform it in Array of Type String.

Second Explode the array.

As an example

WITH table AS(
select 'dummyID' AS id,'codeA' AS code, SPLIT('[{"pmc":"111","scc":"aa1","pgtp":"a22","pgn":"a33","pgrc":"a44"},{"pmc":"222","scc":"bb1","pgtp":"b22","pgn":"b33","pgrc":"b44","sen":"b77"},{"pmc":"333","scc":"cc1","pgtp":"c22","pgn":"c33","pgrc":"c44","pscc":[],"mapb":"c88"},{"pmc":"444","scc":"dd1","pgtp":"d22","pgn":"d33","pgrc":"d44","pscc":["ghgh"],"mapb":"d88"},{"pmc":"555","scc":"ee1","pgtp":"e22","pgn":"e33","pgrc":"e44","mapb":"e88"}]','\\{') AS array)
SELECT id,code, exp_val
FROM   table
LATERAL VIEW explode(array) table AS exp_val;

ouput

+----------+--------+----------------------------------------------------+--+
|    id    |  code  |                      exp_val                       |
+----------+--------+----------------------------------------------------+--+
| dummyID  | codeA  | [                                                  |
| dummyID  | codeA  | "pmc":"111","scc":"aa1","pgtp":"a22","pgn":"a33","pgrc":"a44"}, |
| dummyID  | codeA  | "pmc":"222","scc":"bb1","pgtp":"b22","pgn":"b33","pgrc":"b44","sen":"b77"}, |
| dummyID  | codeA  | "pmc":"333","scc":"cc1","pgtp":"c22","pgn":"c33","pgrc":"c44","pscc":[],"mapb":"c88"}, |
| dummyID  | codeA  | "pmc":"444","scc":"dd1","pgtp":"d22","pgn":"d33","pgrc":"d44","pscc":["ghgh"],"mapb":"d88"}, |
| dummyID  | codeA  | "pmc":"555","scc":"ee1","pgtp":"e22","pgn":"e33","pgrc":"e44","mapb":"e88"}] |
+----------+--------+----------------------------------------------------+--+