在hive中分解map数组结构的字符串

字符串结构如下:

[{"name":"john","id":12,"location":"delhi"},{"name":"raj","id":18,"location":"mumbai"},{"name":"Rahul","id":14,"location":"hyd"}]

需要分解它以创建一个新表，其中列名作为name、id 和location。

实现方式

首先将字符串转换为 JSON 对象数组：删除方括号，在大括号之间用逗号分隔并展开。然后使用带有横向视图的 json_tuple 来提取所有值。

with mytable as (--demo table, use your table instead
select '[{"name":"john","id":12,"location":"delhi"},{"name":"raj","id":18,"location":"mumbai"},{"name":"Rahul","id":14,"location":"hyd"}]' as json_string
)

select --t.json_string as original_string, --commented
      e.pos as position_in_array,
      --values from json
      x.name, x.id, x.location
      
from mytable t 
      lateral view outer posexplode( split(regexp_replace(json_string,'^\\[|\\]$',''),      --remove []
                          '(?<=\\}),(?=\\{)'  --split by comma only after } and before {
                         )                    --converted to array of json strings
                               )e as pos, json --exploded array element with position
       --extract all from e.json 
      lateral view json_tuple(e.json,'name', 'id', 'location') x as name, id, location

结果:

position_in_array  x.name   x.id    x.location  
0                  john     12      delhi
1                  raj      18      mumbai
2                  Rahul    14      hyd