Returning Null JSON Throwing Type Mismatch Error i

问题:

Problem

Description

I'm currently running into a problem where the JSON that I am getting back has fields that are null.

In the code below, I've figured out that most of the fields have an assignee, and down another level assignees have a displayName. I've also found out that some things do not have an assignee. When that happens (and this would probably happen with other fields too, I'm just using this as an example) it removes that additional heirarchy level, and the actual path (also shown below) would be changed.

Question

Is there an easy way to iterate over this response, and set nulls to blanks maybe?

Set Json = JsonConverter.ParseJson(MyRequest.ResponseText)

That doesn't really help me with automation though. Notice [below] where I list components twice, because I don't know how to loop through that data and pull back the field as many times as it needs to be populated. Aka I know there are two components, but it only brings back one component, so I had to copy that code to get it to work correctly (I'm sorry for copying).

Code Snip

My code works great until it hits a null, then it throws an error.

''''''''
' Loop '
''''''''

    For i = 0 To 40

'        ActiveSheet.Cells(i + 1, 1) = Json("issues")(i + 1)("fields")("issuetype")("name")
'        ActiveSheet.Cells(i + 1, 2) = Json("issues")(i)("key")
'        ActiveSheet.Cells(i + 1, 3) = Json("issues")(i + 1)("fields")("summary")
'        ActiveSheet.Cells(i + 1, 4) = Json("issues")(i + 1)("fields")("status")("name")
         ActiveSheet.Cells(i + 1, 5) = Json("issues")(i + 1)("fields")("assignee")
         ActiveSheet.Cells(i + 1, 5) = Json("issues")(i + 1)("fields")("assignee")("displayName")
'        ActiveSheet.Cells(i + 1, 6) = Json("issues")(i + 1)("fields")("customfield_13301")
'        ActiveSheet.Cells(i + 1, 7) = Json("issues")(i + 1)("fields")("components")(1)("name")
'        ActiveSheet.Cells(i + 1, 8) = Json("issues")(i + 1)("fields")("components")(2)("name")
'        ActiveSheet.Cells(i + 1, 9) = Json("issues")(i + 1)("fields")("customfield_13300")
'        ActiveSheet.Cells(i + 1, 10) = Json("issues")(i + 1)("fields")("customfield_10002")
    Next i

JSON

Ovbiously I had to delete some content for privacy reasons, but that shows the assignee as null. JSON with a, "displayName" just turns that null into an Array and has more fields under it.

{
"expand": "schema,names",
"startAt": 0,
"maxResults": 50,
"total": 52,
"issues": [
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{
"expand": "operations,versionedRepresentations,editmeta,changelog,renderedFields",
"id": "92110",
"self": "",
"key": "",
"fields": {
"customfield_13100": null,
"fixVersions": [],
"customfield_13500": null,
"customfield_11200": null,
"resolution": null,
"customfield_13502": null,
"customfield_13501": null,
"lastViewed": null,
"customfield_12000": null,
"customfield_12002": null,
"customfield_12001": null,
"priority": {},
"customfield_10100": null,
"customfield_10101": null,
"customfield_12003": null,
"customfield_12402": null,
"labels": [],
"customfield_11303": null,
"customfield_11305": null,
"customfield_11306": null,
"aggregatetimeoriginalestimate": null,
"timeestimate": null,
"versions": [],
"issuelinks": [],
"assignee": null,
"status": {},
"components": [],
"customfield_13200": null,
"customfield_13600": null,
"customfield_12900": null,
"aggregatetimeestimate": null,
"creator": {},
"customfield_14000": null,
"subtasks": [],
"customfield_14400": null,
"reporter": {},
"customfield_12101": null,
"customfield_12100": null,
"aggregateprogress": {},
"customfield_14401": null,
"customfield_14402": null,
"customfield_12500": null,
"customfield_13702": null,
"customfield_13704": null,
"customfield_13703": null,
"customfield_11802": null,
"progress": {},
"votes": {},
"issuetype": {},
"timespent": null,
"project": {},
"customfield_13300": null,
"aggregatetimespent": null,
"customfield_13302": null,
"customfield_13301": null,
"customfield_13700": null,
"customfield_11400": null,
"resolutiondate": null,
"workratio": -1,
"watches": {},
"created": "2017-07-21T08:04:42.000-0500",
"customfield_14102": null,
"customfield_10020": null,
"customfield_12200": null,
"customfield_14100": null,
"customfield_14101": null,
"customfield_12600": null,
"customfield_14500": null,
"customfield_10300": null,
"customfield_10016": null,
"customfield_13405": null,
"customfield_10017": null,
"customfield_13800": null,
"customfield_10018": null,
"customfield_10019": null,
"customfield_13409": null,
"updated": "2017-08-10T15:29:37.000-0500",
"timeoriginalestimate": null,
"description": null,
"customfield_10011": null,
"customfield_10012": null,
"customfield_13401": null,
"customfield_13400": null,
"customfield_10013": null,
"customfield_10014": null,
"customfield_11500": "{}",
"customfield_10015": null,
"customfield_13514": null,
"summary": "",
"customfield_14200": null,
"customfield_10000": null,
"customfield_13511": null,
"customfield_12301": null,
"customfield_10001": null,
"customfield_12300": null,
"customfield_10002": "1|i021pe:5z",
"customfield_13510": null,
"customfield_13513": null,
"customfield_10003": [],
"customfield_12302": null,
"customfield_10004": null,
"customfield_13504": null,
"customfield_13503": null,
"customfield_11600": null,
"customfield_13506": null,
"environment": null,
"customfield_13901": null,
"customfield_13505": null,
"customfield_13508": null,
"duedate": null,
"customfield_13509": null
}
},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{}
]
}

Additional Data

I looked at the Raw file just to see if anything looked different (than it did in my JSON Formater Plugin for Chrome) and this is what it looked like:

"assignee":null,

回答1:

Working with JSON files is much easier (IMHO) if you understand how the JsonConverter processes the JSON into a compound object. Let's look at a simple JSON format (taken from this useful site):

{
  "array": [
    1,
    2,
    3
  ],
  "boolean": true,
  "null": null,
  "number": 123,
  "object": {
    "a": "b",
    "c": "d",
    "e": "f"
  },
  "string": "Hello World"
}

The JsonConverter maps each of these data items into their VBA counterparts.

"array"   maps to Collection   (anytime you see the square brackets [])
"boolean" maps to Boolean
"null"    maps to Null
"number"  maps to Double
"object"  maps to Dictionary   (anytime you see the curly braces {})
"string"  maps to String

So now we can do useful things with your JSON example, such as determine how many entires are in your "issues" array by

Dim issues As Collection
Set issues = schema("issues")
Debug.Print issues.Count

Each of the entries in your "issues" array is actually a compound object itself, so it's a Dictionary. We could, therefore, do something like this:

Dim issue As Variant
For Each issue In issues
    If issue.Exists("id") Then
        Debug.Print "id = " & issue("id")
    End If
Next issue

Of course, the "fields" section of this single issue is itself another Dictionary. So stacking up the dictionary references we can do this too:

Debug.Print "field summary is " & issue("fields")("summary")

All of this is background, hopefully to make it easier on accessing members of a JSON structure. Your real question is on handling NULLs. If the actual value of a field is set to null (see the above sample), then you check it like so

If IsNull(issue("fields")("customfield_13500")) Then ...

A couple of other side notes before we put it all together:

1. Always use Option Explicit
2. Avoid Select and Activate
3. Always define and set references to all Workbooks and Sheets

In the example below, you'll see that I assumed you had to check each field for Null. That is best accomplished by isolating that check in a subroutine rather than over-mess your code with a long string of If statements. The advantage of the code example below is that you don't have to hard-code the number of issues because your logic can detect how many there are.

Option Explicit

Sub main()
    Dim schema As Object
    Set schema = GetJSON("C:\dev\junk.json")

    Dim thisWB As Workbook
    Dim destSH As Worksheet
    Set thisWB = ThisWorkbook
    Set destSH = thisWB.Sheets("Sheet1")

    Dim anchor As Range
    Set anchor = destSH.Range("A1")

    Dim issues As Collection
    Set issues = schema("issues")

    Dim i As Long
    Dim issue As Variant
    For Each issue In issues
        If issue.Exists("id") Then
            SetCell anchor.Cells(1, 1), issue("fields")("issuetype")("name")
            SetCell anchor.Cells(1, 2), issue("key")
            SetCell anchor.Cells(1, 3), issue("fields")("summary")
            '--- if you're not sure if the "name" field is there,
            '    then remember it's a Dictionary so check with Exists
            If issue("fields")("status").Exists("name") Then
                SetCell anchor.Cells(1, 4), issue("fields")("status")("name")
            Else
                SetCell anchor.Cells(1, 4), vbNullString
            End If
            SetCell anchor.Cells(1, 5), issue("fields")("assignee")
            SetCell anchor.Cells(1, 6), issue("fields")("customfield_13301")
            '--- possibly get the Count and iterate over the exact number of components
            For i = 0 To issue("fields")("components").Count - 1
                SetCell anchor.Cells(1, 7), issue("fields")("components")(i)("name")
            Next i
            SetCell anchor.Cells(1, 9), issue("fields")("customfield_13300")
            SetCell anchor.Cells(1, 10), issue("fields")("customfield_10002")
            Set anchor = anchor.Offset(1, 0)
        End If
    Next issue
End Sub

Function GetJSON(ByVal filename As String) As Object
    '--- first ingest the JSON file and get it parsed
    Dim fso As FileSystemObject
    Dim jsonTS As TextStream
    Dim jsonText As String
    Set fso = New FileSystemObject
    Set jsonTS = fso.OpenTextFile(filename, ForReading)
    jsonText = jsonTS.ReadAll
    Set GetJSON = JsonConverter.ParseJson(jsonText)
End Function

Private Sub SetCell(ByRef thisCell As Range, ByVal thisValue As Variant)
    If IsNull(thisValue) Then
        thisCell = vbNullString
    Else
        thisCell = thisValue
    End If
End Sub

回答2:

Fix

This is what I did to get this to work:

If IsNull(Json("issues")(i + 1)("fields")("components")) Then
    ActiveSheet.Cells(i + 1, 5).Value = ""
Else
    ActiveSheet.Cells(i + 1, 7) = Json("issues")(i + 1)("fields")("components")(1)("name")
End If

回答3:

You may get JSON data into arrays as shown in the example code below. Import JSON.bas module into the VBA project for JSON processing.

Sub Test()

    ' Put sourse JSON string to "\source.json" file, and save as ANSI or Unicode

    Dim sJSONString As String
    Dim vJSON As Variant
    Dim sState As String
    Dim aData()
    Dim aHeader()

    sJSONString = ReadTextFile(ThisWorkbook.Path & "\source.json", -2)
    JSON.Parse sJSONString, vJSON, sState
    vJSON = vJSON("issues")
    JSON.ToArray vJSON, aData, aHeader
    With Sheets(1)
        .Cells.Delete
        .Cells.WrapText = False
        OutputArray .Cells(1, 1), aHeader
        Output2DArray .Cells(2, 1), aData
        .Columns.AutoFit
    End With

End Sub

Sub OutputArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize(1, UBound(aCells) - LBound(aCells) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

Sub Output2DArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize( _
                UBound(aCells, 1) - LBound(aCells, 1) + 1, _
                UBound(aCells, 2) - LBound(aCells, 2) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

Function ReadTextFile(sPath As String, lFormat As Long) As String
    ' lFormat -2 - System default, -1 - Unicode, 0 - ASCII
    With CreateObject("Scripting.FileSystemObject").OpenTextFile(sPath, 1, False, lFormat)
        ReadTextFile = ""
        If Not .AtEndOfStream Then ReadTextFile = .ReadAll
        .Close
    End With
End Function