Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

VBA JsonConverter.ParseJson Parse Jason giving me error Type Mismatch

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

2

Answers


  1. 0

    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-a1en4-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
    Login or Signup to reply.
  2. 0

    The id key is associated with a single value, not an object or array, so

    tempName = JsonData("id")(0)
MsgBox (tempName)   'no parens as noted in BigBen's comment

    should be

    tempName = JsonData("id")
MsgBox tempName
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search