VBA JsonConverter.ParseJson Parse Jason giving me error Type Mismatch

Question

I have below Json value in string groupsjasontext and I like to parse id, members.value, roles.value using JsonConverter.ParseJson. How will I do it ? I tried to show via messagebox for id field and I am getting error Type Mismatch

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

    {
  "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-a1e\n4-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\n",
      "urn:sap:params:scim:schemas:extension:sac:2.0:group-custom-parameters": {
        "description": "View mode EURAM"
      }
    }
  ]
}

Answer 1

Using JSonParser, the required values can be extracted from your sample JSON string as follows;

Sub Test()
    Dim strJSON As String, JSon As Object, id As String, membersValue As String, rolesValue As String
    
    strJSON = "{""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-a1e\n4-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""}}"
      
    Set JSon = ParseJson(strJSON)

    id = JSon("id")
    
    membersValue = JSon("members")(1)("value")
    
    rolesValue = JSon("urn:sap:params:scim:schemas:extension:sac:2.0:group-roles")("roles")(1)("value")

    
    MsgBox "id = " & id & vbCrLf & "members.value = " & membersValue & vbCrLf & "roles.value = " & rolesValue
End Sub