时间戳

首页 > 解决方案 > ORACLE中如何从JSON中提取嵌套节点值

sql - ORACLE中如何从JSON中提取嵌套节点值

问题描述

我正在尝试从 Json 下方提取嵌套节点（以 CLOB 格式存储在名为 DOC 的列中），但它是空白的。

{
  "id": "a3ac3bec-4c4e-42c8-a11c-068f3dfda201",
  "createdDateTime": "2021-08-31T18:00:44Z",
  "userDisplayName": "abc",
  "userPrincipalName": "sad2547@gmail.com",
  "userId": "36a3a1f2-6133-4a0b-a6c9-020693ebdbd3",
  "appId": "1fa516bf-1332-4140-85c9-d844d4e69ca1",
  "appDisplayName": "ProxyIdentityExperienceFramework",
  "ipAddress": "999.99.0.999",
  "clientAppUsed": "Mobile Apps and Desktop clients",
  "correlationId": "c478bdd4-1541-4cd0-bf7e-bd0695325246",
  "conditionalAccessStatus": "notApplied",
  "isInteractive": true,
  "riskDetail": "hidden",
  "riskLevelAggregated": "hidden",
  "riskLevelDuringSignIn": "hidden",
  "riskState": "none",
  "riskEventTypes": [],
  "riskEventTypes_v2": [],
  "resourceDisplayName": "IdentityExperienceFramework",
  "resourceId": "a3c649c7-5daa-4c3f-a5a0-a3fd7281ee20",
  "status": {
    "errorCode": 0,
    "failureReason": "Other.",
    "additionalDetails": null
  },
  "deviceDetail": {
    "deviceId": "",
    "displayName": "",
    "operatingSystem": "Windows 10",
    "browser": "Chrome 92.0.4515",
    "isCompliant": false,
    "isManaged": false,
    "trustType": ""
  },
  "location": {
    "city": "xyz",
    "state": "def",
    "countryOrRegion": "US",
    "geoCoordinates": {
      "altitude": null,
      "latitude": 12.65875,
      "longitude": -74.65286
    }
  },
  "appliedConditionalAccessPolicies": []
}

以下是我的查询：

SELECT x.*
  FROM demo_json a,
       JSON_TABLE(doc, '$'
       COLUMNS (
                ids VARCHAR2(100) PATH '$.id',
             NESTED PATH '$.deviceDetail.*' COLUMNS
            (
             browser VARCHAR2(100) PATH '$.browser'
            )
        )
  ) x;

从 json 中检索所有嵌套节点的任何建议？

谢谢

标签： sqljsonoraclenested

解决方案

您的 JSON 在 userPrincipalName 中包含错误（缺少双引号）："userPrincipalName": sad2547@gmail.com,应该是"userPrincipalName": "sad2547@gmail.com",

如果你修复它，它工作正常：

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=d340a38b993f78170e04750e2aa8013c

with DEMO_JSON (doc) as (
select q'[
{
        "id": "a3ac3bec-4c4e-42c8-a11c-068f3dfda201",
        "createdDateTime": "2021-08-31T18:00:44Z",
        "userDisplayName": "abc",
        "userPrincipalName": "sad2547@gmail.com",
        "userId": "36a3a1f2-6133-4a0b-a6c9-020693ebdbd3",
        "appId": "1fa516bf-1332-4140-85c9-d844d4e69ca1",
        "appDisplayName": "ProxyIdentityExperienceFramework",
        "ipAddress": "999.99.0.999",
        "clientAppUsed": "Mobile Apps and Desktop clients",
        "correlationId": "c478bdd4-1541-4cd0-bf7e-bd0695325246",
        "conditionalAccessStatus": "notApplied",
        "isInteractive": true,
        "riskDetail": "hidden",
        "riskLevelAggregated": "hidden",
        "riskLevelDuringSignIn": "hidden",
        "riskState": "none",
        "riskEventTypes": [],
        "riskEventTypes_v2": [],
        "resourceDisplayName": "IdentityExperienceFramework",
        "resourceId": "a3c649c7-5daa-4c3f-a5a0-a3fd7281ee20",
        "status": {
            "errorCode": 0,
           "failureReason": "Other.",
            "additionalDetails": null
        },
        "deviceDetail": {
            "deviceId": "",
            "displayName": "",
            "operatingSystem": "Windows 10",
            "browser": "Chrome 92.0.4515",
            "isCompliant": false,
            "isManaged": false,
            "trustType": ""
        },
        "location": {
            "city": "xyz",
            "state": "def",
            "countryOrRegion": "US",
            "geoCoordinates": {
                "altitude": null,
                "latitude": 12.65875,
                "longitude": -74.65286
            }
        },
        "appliedConditionalAccessPolicies": []
    }]' doc from dual
)
SELECT
   x.*
FROM DEMO_JSON a,
     JSON_TABLE(a.DOC, '$'
       COLUMNS (
          IDs VARCHAR2(100) PATH id,
          nested path deviceDetail columns(
             browser path browser
          )
       )
     ) X;

推荐阅读