在 VBA 中返回 Null JSON 抛出类型不匹配错误 [英] Returning Null JSON Throwing Type Mismatch Error in VBA

问题描述

问题

说明

我目前遇到了一个问题，即我返回的 JSON 的字段为空.

在下面的代码中，我发现大多数字段都有一个受让人，而另一个级别的受让人有一个 displayName.我还发现有些事情没有受让人.当这种情况发生时(这也可能发生在其他领域，我只是以此为例)它会删除额外的层次结构级别和实际路径(也显示如下) 将被更改.

问题

是否有一种简单的方法可以迭代此响应，并将空值设置为空白?

设置 Json = JsonConverter.ParseJson(MyRequest.ResponseText)

但这并不能真正帮助我实现自动化.请注意 [下面] 我列出了两次组件的地方，因为我不知道如何循环遍历该数据并根据需要填充该字段多次拉回该字段.也就是我知道有两个组件，但它只带回了一个组件，所以我必须复制该代码才能使其正常工作(我很抱歉复制).

代码片段

我的代码运行良好，直到遇到空值，然后抛出错误.

''''''''' 环形 '''''''''对于 i = 0 到 40' ActiveSheet.Cells(i + 1, 1) = Json(问题")(i + 1)(字段")(问题类型")(名称")' ActiveSheet.Cells(i + 1, 2) = Json(问题")(i)(key")' ActiveSheet.Cells(i + 1, 3) = Json(issues")(i + 1)(fields")(summary")' ActiveSheet.Cells(i + 1, 4) = Json(问题")(i + 1)(字段")(状态")(名称")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(问题")(i + 1)(字段")(组件")(1)(名称")' ActiveSheet.Cells(i + 1, 8) = Json(问题")(i + 1)(字段")(组件")(2)(名称")' ActiveSheet.Cells(i + 1, 9) = Json(问题")(i + 1)(字段")(customfield_13300")' ActiveSheet.Cells(i + 1, 10) = Json(问题")(i + 1)(字段")(customfield_10002")接下来我

JSON

显然出于隐私原因我不得不删除一些内容，但这显示受让人为空.带有displayName"的 JSON只是将那个空值变成一个数组并在它下面有更多的字段.

<代码>{扩展":架构，名称"，开始":0，最大结果":50，总计":52，问题":[{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{扩展":操作，版本化表示，编辑元，变更日志，渲染字段"，id":92110"，自我":"，钥匙":"，字段":{customfield_13100":空，修复版本":[]，customfield_13500":空，customfield_11200":空，分辨率":空，customfield_13502":空，customfield_13501":空，lastViewed":空，customfield_12000":空，customfield_12002":空，customfield_12001":空，优先级":{}，customfield_10100":空，customfield_10101":空，customfield_12003":空，customfield_12402":空，标签":[]，customfield_11303":空，customfield_11305":空，customfield_11306":空，aggregatetimeoriginalestimate":null，时间估计":空，版本":[]，问题链接":[]，受让人":空，状态":{}，组件":[]，customfield_13200":空，customfield_13600":空，customfield_12900":空，aggregatetimeestimate":空，创建者":{}，customfield_14000":空，子任务":[]，customfield_14400":空，记者":{}，customfield_12101":空，customfield_12100":空，聚合进度":{}，customfield_14401":空，customfield_14402":空，customfield_12500":空，customfield_13702":空，customfield_13704":空，customfield_13703":空，customfield_11802":空，进度":{}，投票":{}，问题类型":{}，时间花费":空，项目":{}，customfield_13300":空，aggregatetimespent":空，customfield_13302":空，customfield_13301":空，customfield_13700":空，customfield_11400":空，解决日期":空，工作比率":-1，手表":{}，创建":2017-07-21T08:04:42.000-0500"，customfield_14102":空，customfield_10020":空，customfield_12200":空，customfield_14100":空，customfield_14101":空，customfield_12600":空，customfield_14500":空，customfield_10300":空，customfield_10016":空，customfield_13405":空，customfield_10017":空，customfield_13800":空，customfield_10018":空，customfield_10019":空，customfield_13409":空，更新":2017-08-10T15:29:37.000-0500"，时间原始估计":空，描述":空，customfield_10011":空，customfield_10012":空，customfield_13401":空，customfield_13400":空，customfield_10013":空，customfield_10014":空，customfield_11500":{}"，customfield_10015":空，customfield_13514":空，摘要":"，customfield_14200":空，customfield_10000":空，customfield_13511":空，customfield_12301":空，customfield_10001":空，customfield_12300":空，customfield_10002":1|i021pe:5z"，customfield_13510":空，customfield_13513":空，customfield_10003":[]，customfield_12302":空，customfield_10004":空，customfield_13504":空，customfield_13503":空，customfield_11600":空，customfield_13506":空，环境":空，customfield_13901":空，customfield_13505":空，customfield_13508":空，截止日期":空，customfield_13509":空}},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{}]}

其他数据

我查看原始文件只是想看看是否有什么不同(与我的 JSON Formater Plugin for Chrome 相比)，这就是它的样子:

受让人":空，

解决方案

如果您了解 JsonConverter 如何将 JSON 处理为复合对象，那么使用 JSON 文件会容易得多 (恕我直言).让我们看一个简单的 JSON 格式(取自 这个有用的网站):

<代码>{大批": [1、2、3],布尔值":对，空":空，数字":123，目的": {"a": "b","c": "d",e":f"},"string": "你好世界"}

JsonConverter 将这些数据项中的每一个映射到它们的 VBA 对应项中.

<块引用>

"array" 映射到 Collection(任何时候你看到方括号 [])boolean"映射到布尔值空"映射到空数字"映射到双对象"映射到字典(任何时候你看到花括号 {})字符串"映射到字符串

所以现在我们可以对您的 JSON 示例做一些有用的事情，例如通过

确定您的 "issues" 数组中有多少个整数

将问题作为集合变暗设置问题 = 架构(问题")调试.打印问题.计数

"issues" 数组中的每个条目实际上都是一个复合对象本身，因此它是一个 Dictionary.因此，我们可以这样做:

Dim issue As Variant对于每个问题在问题中如果 issue.Exists("id") 然后Debug.Print "id = " &问题(ID")万一下一期

当然，这个issue 的"fields" 部分本身就是另一个Dictionary.所以堆叠字典参考我们也可以这样做:

Debug.Print "字段摘要为" &问题(字段")(摘要")

所有这些都是背景，希望可以更轻松地访问 JSON 结构的成员.你真正的问题是处理NULLs.如果一个字段的实际值被设置为 null(见上面的例子)，那么你像这样检查它

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

在我们把它们放在一起之前，还有一些其他的注意事项:

1. 总是使用Option Explicit
2. 避免Select和Activate
3. 始终定义和设置对所有工作簿和工作表的引用

在下面的示例中，您将看到我假设您必须检查每个字段的 Null.这最好通过在子例程中隔离该检查来实现，而不是用一长串 If 语句过度混乱您的代码.下面的代码示例的优点是您不必对问题的数量进行硬编码，因为您的逻辑可以检测到有多少.

选项显式子主()Dim 模式作为对象Set schema = GetJSON("C:devjunk.json")将此WB 变暗为工作簿Dim destSH 作为工作表设置 thisWB = ThisWorkbook设置 destSH = thisWB.Sheets("Sheet1")将锚定为范围设置锚点 = destSH.Range("A1")Dim 问题作为集合设置问题 = 架构(问题")昏暗的我Dim issue As Variant对于每个问题在问题中如果 issue.Exists("id") 然后SetCell anchor.Cells(1, 1), issue("fields")("issuetype")("name")SetCell anchor.Cells(1, 2), issue("key")SetCell anchor.Cells(1, 3), issue("fields")("summary")'--- 如果您不确定名称"字段是否在那里，' 然后记住它是一个字典所以检查 ExistsIf issue("fields")("status").Exists("name") ThenSetCell anchor.Cells(1, 4), issue("fields")("status")("name")别的SetCell anchor.Cells(1, 4), vbNullString万一SetCell anchor.Cells(1, 5), issue("fields")("assignee")SetCell anchor.Cells(1, 6), issue("fields")("customfield_13301")'--- 可能获得计数并迭代组件的确切数量对于 i = 0 发出(字段")(组件").计数 - 1SetCell anchor.Cells(1, 7), issue("fields")("components")(i)("name")接下来我SetCell anchor.Cells(1, 9), issue("fields")("customfield_13300")SetCell anchor.Cells(1, 10), issue("fields")("customfield_10002")设置锚点 = anchor.Offset(1, 0)万一下一期结束子函数 GetJSON(ByVal filename As String) 作为对象'--- 首先摄取 JSON 文件并解析它将 fso 调暗为 FileSystemObject将 jsonTS 调暗为 TextStream将 jsonText 调暗为字符串设置 fso = 新文件系统对象设置 jsonTS = fso.OpenTextFile(filename, ForReading)jsonText = jsonTS.ReadAll设置 GetJSON = JsonConverter.ParseJson(jsonText)结束函数Private Sub SetCell(ByRef thisCell As Range, ByVal thisValue As Variant)如果 IsNull(thisValue) 那么thisCell = vbNullString别的thisCell = thisValue万一结束子

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,

解决方案

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:devjunk.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

这篇关于在 VBA 中返回 Null JSON 抛出类型不匹配错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！