VBA使用JsonConverter.ParseJson解析JSON遇类型不匹配错误的解决

问题场景 #

我把SCIM Group的JSON数据存在字符串groupsjasontext里，想用JsonConverter.ParseJson解析id、members.value、roles.value字段，但运行以下代码时出现「Type Mismatch（类型不匹配）」错误：

Set JsonData = JsonConverter.ParseJson(groupsjasontext)
tempName = JsonData("id")(0)
MsgBox (tempName)

对应的JSON数据：

{
  "schemas": [
    "urn:ietf:params:scim:api:messages:2.0:ListResponse"
  ],
  "totalResults": 3,
  "startIndex": 1,
  "itemsPerPage": 3,
  "Resources": [
    {
      "schemas": [
        "urn:ietf:params:scim:schemas:core:2.0:Group",
        "urn:sap:params:scim:schemas:extension:sac:2.0:group-custom-parameters"
      ],
      "id": "d3094970-ce7e-4794-b9e2-f84817b7c820",
      "meta": {
        "resourceType": "Group",
        "created": "2024-05-21T17:53:06.808Z",
        "lastModified": "2024-05-21T17:53:06.808Z",
        "location": "/api/v1/scim2/Groups/d3094970-ce7e-4794-b9e2-f84817b7c820"
      },
      "displayName": "AHI_VW_ALL_REGION",
      "urn:sap:params:scim:schemas:extension:sac:2.0:group-custom-parameters": {
        "description": "View Mode ALL REGION"
      }
    },
    {
      "schemas": [
        "urn:ietf:params:scim:schemas:core:2.0:Group",
        "urn:sap:params:scim:schemas:extension:sac:2.0:group-roles",
        "urn:sap:params:scim:schemas:extension:sac:2.0:group-custom-parameters"
      ],
      "id": "bef561ee-5a1e-420e-a1e4-4f624c96af6e",
      "meta": {
        "resourceType": "Group",
        "created": "2024-07-25T14:17:53.215Z",
        "lastModified": "2024-08-01T14:31:29.632Z",
        "location": "/api/v1/scim2/Groups/bef561ee-5a1e-420e-a1e4-4f624c96af6e"
      },
      "displayName": "TEAM_TEST",
      "members": [
        {
          "value": "f27dcbb9-df9a-46b2-b23a-3b35d5a8bdff",
          "type": "User",
          "display": "Test_FirstName1 TEST_Familyname1",
          "$ref": "/api/v1/scim2/Users/f27dcbb9-df9a-46b2-b23a-3b35d5a8bdff"
        }
      ],
      "urn:sap:params:scim:schemas:extension:sac:2.0:group-roles": {
        "roles": [
          {
            "value": "PROFILE:t.4:VIEW_USER",
            "display": "VIEW_USER"
          }
        ]
      },
      "urn:sap:params:scim:schemas:extension:sac:2.0:group-custom-parameters": {
        "description": "Team Test"
      }
    },
    {
      "schemas": [
        "urn:ietf:params:scim:schemas:core:2.0:Group",
        "urn:sap:params:scim:schemas:extension:sac:2.0:group-custom-parameters"
      ],
      "id": "1edd9227-c8de-486e-93f6-69396524c792",
      "meta": {
        "resourceType": "Group",
        "created": "2024-05-21T17:51:49.808Z",
        "lastModified": "2024-05-21T17:53:06.121Z",
        "location": "/api/v1/scim2/Groups/1edd9227-c8de-486e-93f6-69396524c792"
      },
      "displayName": "AHI_VW_EURAM",
      "urn:sap:params:scim:schemas:extension:sac:2.0:group-custom-parameters": {
        "description": "View mode EURAM"
      }
    }
  ]
}

错误原因 #

原代码直接访问JsonData("id")，但id字段并非顶层属性，而是嵌套在Resources数组的每个元素里，必须遍历Resources数组才能正确读取目标字段。

正确解析代码 #

Dim JsonData As Object
Dim Resources As Object
Dim Resource As Object
Dim Member As Object
Dim Role As Object

Set JsonData = JsonConverter.ParseJson(groupsjasontext)
Set Resources = JsonData("Resources")

' 遍历每个资源组
For Each Resource In Resources
    ' 获取id
    MsgBox "ID: " & Resource("id")
    
    ' 获取members.value（如果存在）
    If Resource.Exists("members") Then
        For Each Member In Resource("members")
            MsgBox "Member Value: " & Member("value")
        Next Member
    End If
    
    ' 获取roles.value（如果存在）
    If Resource.Exists("urn:sap:params:scim:schemas:extension:sac:2.0:group-roles") Then
        For Each Role In Resource("urn:sap:params:scim:schemas:extension:sac:2.0:group-roles")("roles")
            MsgBox "Role Value: " & Role("value")
        Next Role
    End If
Next Resource

内容的提问来源于stack exchange，提问作者Lord OfTheRing