VBA/JIRA/JSON: add new key/value to dictionary parsed from a JSON

Question

I am working on a piece of code to extract issues from a JIRA project and then loop through each issue to see if it already exists within an Excel sheet. For either outcome, I want to add a new key-value combination which will essentially tag whether the issue exists or not, e.g. "exists": "true".

I am using the JSONConverter (VBA-JSON) code by Tim Hall to parse the JSON response into an Excel dictionary. Now I am struggling to understand the correct syntax in order to add the new key-value into the dictionary.

Example JSON:

"issues": [{
      "expand": "operations,editmeta,changelog,transitions,renderedFields",
      "id": "123456789",
      "self": "url",
      "key": "XY-12345",
      "fields": {
            "issuetype": {
                      "self": "url",
                      "id": "1",
                      "description": descrip.",
                      "iconUrl": "url",
                      "name": "Story",
                      "subtask": false
                        },
                },
           },

This is what I am trying to produce (if the dictionary was parsed back to JSON; see 'exists'):

"issues": [{
      "expand": "operations,editmeta,changelog,transitions,renderedFields",
      "id": "123456789",
      "self": "url",
      "key": "XY-12345",
      "exists": "true",
      "fields": {
            "issuetype": {
                      "self": "url",
                      "id": "1",
                      "description": descrip.",
                      "iconUrl": "url",
                      "name": "Story",
                      "subtask": false
                        },
                },
           },

In terms of code, once I have retrieved the JSON from JIRA, I convert using:

Dim oDict as dictionary
Set oDict = ParseJSON(sJSON)

And then I try to add the new item into the dictionary by looping through all issues:

for n=1 to oDict("issues").count
    If dotfind(oDict("issues")(n)("key"),"r",sht) = 0 Then '//function to search if key exists
        oDict.Add ("issues")(n)("exists"), "false"
    Else
        oDict.Add ("issues")(n)("exists"), "true"
    End if
next n

In the end, I'd like to be able to call the below to get the value for exists

Cells(r,c) = oDict("issues")(n)("exists")

Answer 1

This worked for me, HTH.

Private Const sJSON As String = "{" & _
    """issues"": [{" & _
        """expand"": ""operations,editmeta,changelog,transitions,renderedFields""," & _
        """id"": ""123456789""," & _
        """self"": ""url""," & _
        """key"": ""XY-12345""," & _
        """fields"": {" & _
            """issuetype"": {" & _
                """self"": ""url""," & _
                """id"": ""1""," & _
                """description"": ""descrip.""," & _
                """iconUrl"": ""url""," & _
                """name"": ""Story""," & _
                """subtask"": ""false""" & _
            "}" & _
        "}" & _
    "}]" & _
"}"

Sub test()
    Dim sht

    Dim oDict As Scripting.Dictionary
    Set oDict = ParseJson(sJSON)

    Dim issue
    For Each issue In oDict("issues")
        If dotfind(issue("key"), "r", sht) = 0 Then '//function to search if key exists
            issue.Add "exists", "false"
        Else
            issue.Add "exists", "true"
        End If
    Next

    Dim result
    result = ConvertToJson(oDict)

    Debug.Print result

    Dim r, c, n
    r = 1
    c = 1
    n = 1
    Cells(r, c) = oDict("issues")(n)("exists") ' Writes false to "A1"

End Sub

Private Function dotfind(a, b, c) As Integer
    dotfind = 0
End Function

Output

{
    "issues": [{
        "expand": "operations,editmeta,changelog,transitions,renderedFields",
        "id": "123456789",
        "self": "url",
        "key": "XY-12345",
        "fields": {
            "issuetype": {
                "self": "url",
                "id": "1",
                "description": "descrip.",
                "iconUrl": "url",
                "name": "Story",
                "subtask": "false"
            }
        },
        "exists": "false"
    }]
}

Validated with JSONLint.

Answer 2

Try to change your code as follows:

For n = 1 To oDict("issues").Count
    If dotfind(oDict("issues")(n)("key"), "r", sht) = 0 Then '//function to search if key exists
        oDict("issues")(n).Add "exists", "false"
    Else
        oDict("issues")(n).Add "exists", "true"
    End If
Next n