Snowflake中处理JSON数组([])及查询字段为Null的问题

问题背景 #

在Snowflake中查询Okta事件数据时，执行以下SQL：

select
    PARSE_JSON(src):"eventType"::STRING AS eventtype,
    PARSE_JSON(PARSE_JSON(src):"data"::STRING):"events" AS events,
    PARSE_JSON(PARSE_JSON(PARSE_JSON(src):"data"::STRING):"events"):"actor" AS actor,
    PARSE_JSON(PARSE_JSON(PARSE_JSON(PARSE_JSON(src):"data"::STRING):"events"):"actor"):"actor" AS actor2,
    PARSE_JSON(PARSE_JSON(PARSE_JSON(src):"data"::STRING):"events"):"client" AS client,
    * 
from stage.okta_events

查询结果中eventtype和events能返回有效数据，但actor、actor2、client字段均为Null。

对应的src字段JSON结构示例：

{
  "eventType": "com.okta.event_hook",
  "data": {
    "events": [
      {
        "actor": {
          "id": "00uirbgg60V6N7f2T2p7",
          "alternateId": "giovannia1703@outlook.com"
        },
        "client": {
          "ipAddress": "75.181.198.11",
          "device": "Computer"
        }
      }
    ]
  }
}

问题原因 #

data.events是JSON数组（用[]包裹），而非单个JSON对象。原SQL直接尝试从数组对象中读取actor/client属性，但数组本身没有这些属性，因此返回Null。

解决方案 #

场景1：数组仅含单个元素 #

直接通过数组索引[0]访问第一个元素，同时简化冗余的PARSE_JSON调用：

select
    PARSE_JSON(src):"eventType"::STRING AS eventtype,
    PARSE_JSON(src):data:"events" AS events,
    -- 读取数组第一个元素的actor对象
    PARSE_JSON(src):data:"events"[0]:"actor" AS actor,
    -- 读取actor下的具体字段
    PARSE_JSON(src):data:"events"[0]:"actor":"alternateId"::STRING AS actor_email,
    -- 读取数组第一个元素的client对象
    PARSE_JSON(src):data:"events"[0]:"client" AS client,
    * 
from stage.okta_events

场景2：数组包含多个元素 #

使用LATERAL FLATTEN展开数组，将每个数组元素转为单独的行：

select
    PARSE_JSON(src):"eventType"::STRING AS eventtype,
    -- 展开后的单个event对象
    value AS event,
    -- 直接从展开的对象中读取字段
    value:"actor" AS actor,
    value:"actor":"alternateId"::STRING AS actor_email,
    value:"client" AS client,
    * 
from stage.okta_events,
LATERAL FLATTEN(input => PARSE_JSON(src):data:"events")

关键优化说明 #

• Snowflake支持JSON对象的链式访问，无需多次嵌套调用PARSE_JSON，仅需对原始字符串src解析一次即可。
• 针对JSON数组，必须先通过索引（单元素场景）或FLATTEN（多元素场景）获取到数组内的具体对象，再读取其属性。

内容的提问来源于stack exchange，提问作者Scott Wood