SQL复杂数据类型处理

背景

数据处理中，经常碰到复杂数据类型，需要将他们进行解析才能利用。

复杂数据类型

1、MAP结构转为列

WITH tmp AS (
SELECT  '{"Users":{"4418":{"UserId":4418,"Score":0,"IsStudent":true},"5866":{"UserId":5866"Score":0,"IsStudent":false}}}' AS js
)
SELECT
uid
,info
--    ,FROM_JSON(GET_JSON_OBJECT(js,'$.Users'),'MAP<STRING,STRING>')
FROM tmp AS t1
LATERAL VIEW EXPLODE(FROM_JSON(GET_JSON_OBJECT(js,'$.Users'),'MAP<STRING,STRING>')) t1 AS uid,info
;