json - 从 MS Access 在 VBA 中解析 JSON (US BLS)

Question

预先感谢您的帮助。

我正在使用 JSON VB6 解析器，可以在以下位置找到:VB JSON Parser

我有以下 JSON 响应(来自 BLS 网站，特别是此链接 Here:

{"status":"REQUEST_SUCCEEDED","responseTime":71,"message":[],"Results":{ "series": [{"seriesID":"WPS012","data":[{"year":"2014","period":"M11","periodName":"November","value":"153.6","footnotes":[{"code":"P","text":"Preliminary. All indexes are subject to revision four months after original publication."}]},{"year":"2014","period":"M10","periodName":"October","value":"147.4","footnotes":[{"code":"P","text":"Preliminary. All indexes are subject to revision four months after original publication."}]},{"year":"2014","period":"M09","periodName":"September","value":"146.5","footnotes":[{"code":"P","text":"Preliminary. All indexes are subject to revision four months after original publication."}]},{"year":"2014","period":"M08","periodName":"August","value":"156.9","footnotes":[{"code":"P","text":"Preliminary. All indexes are subject to revision four months after original publication."}]},{"year":"2014","period":"M07","periodName":"July","value":"156.4","footnotes":[{}]},{"year":"2014","period":"M06","periodName":"June","value":"179.6","footnotes":[{}]},{"year":"2014","period":"M05","periodName": "May","value":"205.4","footnotes":[{}]},{"year":"2014","period":"M04","periodName":"April","value":"201.6","footnotes":[{}]},{"year":"2014","period":"M03","periodName":"March","value":"188.1","footnotes":[{}]},{"year":"2014","period":"M02","periodName":"February","value":"180.2","footnotes":[{}]},{"year":"2014","period":"M01","periodName":"January","value":"177.8","footnotes":[{}]},{"year":"2013","period":"M12","periodName":"December","value":"183.2","footnotes":[{}]},{"year":"2013","period":"M11","periodName":"November","value":"180.4","footnotes":[{}]},{"year":"2013","period":"M10","periodName":"October","value":"186.4","footnotes":[{}]},{"year":"2013","period":"M09","periodName":"September","value":"197.1","footnotes":[{}]},{"year":"2013","period":"M08","periodName":"August","value":"222.2","footnotes":[{}]},{"year":"2013","period":"M07","periodName":"July","value":"252.9","footnotes":[{}]},{"year":"2013","period":"M06","periodName":"June","value":"259.0","footnotes":[{}]},{"year":"2013","period":"M05","p eriodName":"May","value":"263.7","footnotes":[{}]},{"year":"2013","period":"M04","periodName":"April","value":"249.3","footnotes":[{}]},{"year":"2013","period":"M03","periodName":"March","value":"268.1","footnotes":[{}]},{"year":"2013","period":"M02","periodName":"February","value":"267.1","footnotes":[{}]},{"year":"2013","period":"M01","periodName":"January","value":"279.7","footnotes":[{}]},{"year":"2012","period":"M12","periodName":"December","value":"283.2","footnotes":[{}]},{"year":"2012","period":"M11","periodName":"November","value":"280.8","footnotes":[{}]},{"year":"2012","period":"M10","periodName":"October","value":"286.7","footnotes":[{}]},{"year":"2012","period":"M09","periodName":"September","value":"285.2","footnotes":[{}]},{"year":"2012","period":"M08","periodName":"August","value":"298.9","footnotes":[{}]},{"year":"2012","period":"M07","periodName":"July","value":"275.8","footnotes":[{}]},{"year":"2012","period":"M06","periodName":"June","value":"226.9","footnotes":[{}]},{"year":"2012","perio d":"M05","periodName":"May","value":"233.7","footnotes":[{}]},{"year":"2012","period":"M04","periodName":"April","value":"239.9","footnotes":[{}]},{"year":"2012","period":"M03","periodName":"March","value":"243.6","footnotes":[{}]},{"year":"2012","period":"M02","periodName":"February","value":"239.9","footnotes":[{}]},{"year":"2012","period":"M01","periodName":"January","value":"243.8","footnotes":[{}]}]}] }}`

我能够使用解析器返回“状态”、“响应时间”和“消息”。除此之外的任何事情(第二个大括号的开头)我什么也得不到。

以下是我尝试使用的代码:

Dim p As Object
Set p = JSON.parse(gbl_response)


'Print the text of a nested property '
Debug.Print p.Item("responseTime")
'Print the text of a property within an array '
Debug.Print p.Item("Results").Item("series").Item("seriesID")

p.Item("responseTime") 的打印工作并返回“71”，但是我在第二次打印尝试时收到“无效的调用过程或参数”错误。

对于我的生活，我四处寻找，但没有找到任何解决方案。我试过 this这看起来几乎相同，但唉，我试图在这里复制解决方案，但似乎没有奏效。

感谢您的帮助!

Answer 1

Public Const jsonSource As String = "{" & _
  """status"": ""REQUEST_SUCCEEDED"", " & _
  """responseTime"": 71, " & _
  """message"": [ " & _
  "], " & _
  """Results"": { " & _
    """series"": [ " & _
      "{ " & _
        """seriesID"": ""WPS012"", " & _
        """data"": [ " & _
          "{ " & _
            """year"": ""2014"", " & _
            """period"": ""M11"", " & _
            """periodName"": ""November"", " & _
            """value"": ""153.6"", " & _
            """footnotes"": [ " & _
              "{ " & _
                """code"": ""P"", " & _
                """text"": ""Preliminary. All indexes are subject to revision four months after original publication."" " & _
              "} " & _
            "] " & _
          "} " & _
        "] " & _
      "}]}}"

Sub JsonTest()
    Dim jsonData As Scripting.Dictionary
    Set jsonData = JSON.parse(jsonSource)

    Dim responseTime As String
    responseTime = jsonData("responseTime")

    Dim results As Scripting.Dictionary
    Set results = jsonData("Results")

    Dim series As Collection
    Set series = results("series")

    Dim seriesItem As Scripting.Dictionary
    For Each seriesItem In series
        Dim seriesId As String
        seriesId = seriesItem("seriesID")
        Debug.Print seriesId

        Dim data As Collection
        Set data = seriesItem("data")

        Dim dataItem As Scripting.Dictionary
        For Each dataItem In data
            Dim year As String
            year = dataItem("year")

            Dim period As String
            period = dataItem("period")

            Dim periodName As String
            periodName = dataItem("periodName")

            Dim value As String
            value = dataItem("value")

            Dim footnotes As Collection
            Set footnotes = dataItem("footnotes")

            Dim footnotesItem As Scripting.Dictionary
            For Each footnotesItem In footnotes
                Dim code As String
                code = footnotesItem("code")

                Dim text As String
                text = footnotesItem("text")

            Next footnotesItem
        Next dataItem
    Next seriesItem
End Sub