Блог

Я пытаюсь проанализировать все массивы и вложенные значения в данном Json, но для вложенных блоков вместо одного создается 3 отдельных повторения. Есть ли способ отобразить только одну запись вместо 3, не объединяя или не группируя по?

 "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#auditLogs/signIns",
"value": [
    {
    "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": []
},
{
    "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": 1,
       "failureReason": "Other.",
        "additionalDetails": null
    },
    "deviceDetail": {
        "deviceId": "",
        "displayName": "",
        "operatingSystem": "Windows 10",
        "browser": "Chrome 92.0.4505",
        "isCompliant": false,
        "isManaged": false,
        "trustType": ""
    },
    "location": {
        "city": "abc",
        "state": "def",
        "countryOrRegion": "US",
        "geoCoordinates": {
            "altitude": null,
            "latitude": 12.65875,
            "longitude": -74.65286
        }
    },
    "appliedConditionalAccessPolicies": []
]
 

}

SQL:

 SELECT   x.*
FROM DEMO_JSON a,
     JSON_TABLE(a.DOC, '$.value[*]'
       COLUMNS (
          IDs VARCHAR2(100) PATH id,
          nested path status columns(
             code path errorCode),
          nested path deviceDetail columns(
             browser path browser
          ),
          nested path location columns(
             city path city)
       )
     ) X;
 

Выход:

 IDS                                   CODE  BROWSER          CITY
a3ac3bec-4c4e-42c8-a11c-068f3dfda201    0       
a3ac3bec-4c4e-42c8-a11c-068f3dfda201        Chrome 92.0.4515    
a3ac3bec-4c4e-42c8-a11c-068f3dfda201                          xyz
a3ac3bec-4c4e-42c8-a11c-068f3dfda201    1       
a3ac3bec-4c4e-42c8-a11c-068f3dfda201        Chrome 92.0.4505    
a3ac3bec-4c4e-42c8-a11c-068f3dfda201                          abc```