sql - 在 Snowflake 中解析 JSON

Question

我正在尝试使用 Snowflake 中的后一个函数解析 Snowflake 中的以下嵌套 JSON，但我希望“GoalTime”中的每个嵌套列都显示为一列。例如，

GoalTime_InDoorOpen         
2020-03-26T12:58:00-04:00   

GoalTime_InLastOff
null

GoalTime_OutStartBoarding
2020-03-27T14:00:00-04:00  

"GoalTime": [
    {
      "GoalName": "GoalTime_InDoorOpen",
      "GoalTime": "2020-03-26T12:58:00-04:00"
    },
    {
      "GoalName": "GoalTime_InLastOff"
    },
    {
      "GoalName": "GoalTime_InReadyToTow"
    },
    {
      "GoalName": "GoalTime_OutTowAtGate"
    },
    {
      "GoalName": "GoalTime_OutStartBoarding",
      "GoalTime": "2020-03-27T14:00:00-04:00"
    },

Answer 1

或者如果您有很多行(看起来是航类)，因此您需要每个航类的列，此代码就是您想要的内容

with data as (
select flight_code, parse_json(json) as json from values ('nz101','{GoalTime:[{"GoalName": "GoalA", "GoalTime": "2020-03-26T12:58:00-04:00"}, {"GoalName": "GoalB"}]}'),
                                                 ('nz201','{GoalTime:[{"GoalName": "GoalA"}, {"GoalName": "GoalB", "GoalTime": "2020-03-26T12:58:00-02:00"}]}') 
                                                 j(flight_code, json)
), unrolled as (
select d.flight_code, f.value:GoalName as goal_name, f.value:GoalTime as goal_time
from data d,
lateral flatten (input => json:GoalTime) f
)
select * 
from unrolled
    pivot(min(goal_time) for goal_name in ('GoalA', 'GoalB'))
order by flight_code;

它给出了结果:

FLIGHT_CODE 'GoalA'                       'GoalB'
nz101       "2020-03-26T12:58:00-04:00"   null
nz201       null                          "2020-03-26T12:58:00-02:00"