ORACLE数据库如何提取JSON数据中的嵌套节点值

问题原因 #

你当前查询返回空的核心原因是NESTED PATH的路径配置错误：$.deviceDetail.*代表遍历deviceDetail对象下的所有属性值（比如直接取到"Chrome 92.0.4515"、"Windows 10"这些单个值，而非deviceDetail整个对象），此时在这个层级下不存在$.browser节点，因此返回空。

解决方案 #

你示例中的嵌套节点（deviceDetail、location、status等）都是单个JSON对象，而非JSON数组，这种场景不需要使用NESTED PATH语法，直接在COLUMNS中指定完整的JSON路径即可提取所有嵌套节点值，示例写法如下：

SELECT x.*
FROM demo_json a,
     JSON_TABLE(a.doc, '$'
     COLUMNS (
       ids VARCHAR2(100) PATH '$.id',
       created_time TIMESTAMP PATH '$.createdDateTime',
       user_name VARCHAR2(100) PATH '$.userDisplayName',
       user_principal_name VARCHAR2(200) PATH '$.userPrincipalName',
       app_name VARCHAR2(200) PATH '$.appDisplayName',
       ip_address VARCHAR2(50) PATH '$.ipAddress',
       -- 提取status下的嵌套字段
       status_error_code NUMBER PATH '$.status.errorCode',
       status_failure_reason VARCHAR2(200) PATH '$.status.failureReason',
       -- 提取deviceDetail下的嵌套字段
       device_browser VARCHAR2(100) PATH '$.deviceDetail.browser',
       device_os VARCHAR2(100) PATH '$.deviceDetail.operatingSystem',
       device_is_managed NUMBER(1) PATH '$.deviceDetail.isManaged',
       -- 提取location下的嵌套字段，包括多层嵌套的geoCoordinates
       location_city VARCHAR2(100) PATH '$.location.city',
       location_country VARCHAR2(10) PATH '$.location.countryOrRegion',
       geo_latitude NUMBER PATH '$.location.geoCoordinates.latitude',
       geo_longitude NUMBER PATH '$.location.geoCoordinates.longitude'
     )
) x;

如果遇到嵌套数组需要遍历的场景（比如你示例中的appliedConditionalAccessPolicies数组如果存在有效值的话），才需要使用NESTED PATH语法，写法参考如下：

SELECT x.*
FROM demo_json a,
     JSON_TABLE(a.doc, '$'
     COLUMNS (
       ids VARCHAR2(100) PATH '$.id',
       user_name VARCHAR2(100) PATH '$.userDisplayName',
       -- 遍历数组类型的嵌套节点
       NESTED PATH '$.appliedConditionalAccessPolicies[*]' COLUMNS (
         policy_id VARCHAR2(100) PATH '$.id',
         policy_name VARCHAR2(100) PATH '$.displayName',
         policy_result VARCHAR2(50) PATH '$.result'
       )
     )
) x;

内容的提问来源于stack exchange，提问作者Aamir Gudawala