Json within a array

[moeharoon]

I'm sure this is simple, but I can't seem to get the value of accountId [ { "securitiesAccount" : { "accountId" : "123", ... }, "currentBalances" : { "cashBalance" : 1.0, } }, { "securitiesAccount" : { "accountId" : "456", ... }, "currentBalances" : { "cashBalance" : 1.0, } } ]

Here is my sample code Dim Json As Object Set Json = JsonConverter.ParseJson(httpGet) Debug.Print Json("securitiesAccount")("accountId")

I'm struggling with getting the array as the starting point I can run the following and see 2 json counts Debug.Print Json.Count

Thank you in advance for your help

[houghtonap]

You might want to refer to my comment on issue 195 and comment on issue 202 they have a number of useful notes and links.

1. If you want intellisense in the VBA editor you will need to use the correct objects instead of Variant or Object. VBA-JSON maps JSON Arrays to a VBA.collection and JSON Objects to a Scripting.Dictionary (you will need to add a project reference to this system COM object).
2. You cannot just add indices in VBA to the object returned from JsonConverter, like you would in JavaScript using [ ] and dot notation.
3. You have to program against two different object. Their methods are different.

Option Explicit

' Dependency, requires VBA Reference to "Microsoft Scripting Runtime"
' Dependency, requires the VBA-JSON module to be imported into the VBA Project

Public Sub Issue246()

  Const mesg As String = _
  "[" & _
  "  { ""securitiesAccount"" : { ""accountId"" : ""123"" }, ""currentBalances"" : { ""cashBalance"" : 1.0 } }," & _
  "  { ""securitiesAccount"" : { ""accountId"" : ""456"" }, ""currentBalances"" : { ""cashBalance"" : 1.0 } } " & _
  "]"

  Dim json As Object                ' Reference to object created by VBA-JSON.
  Dim item As Object                ' Reference until we know what type of VBA object it is.

  Dim arry As VBA.Collection        ' Reference to the top level JSON Array.
  Dim dict As Scripting.Dictionary  ' Reference to the JSON Object in the JSON Array.
  Dim acct As Scripting.Dictionary  ' Reference to the JSON Object of key securitiesAccount.

  Set json = ParseJson(mesg)

  ' First let's make sure that the Json parsed was a Json Array.
  ' Note, depending on the API being used checking for a successful message could be much more
  '  complicated since the API could return a JSON Object for success and a JSON Array, Object,
  '  String, Boolean, Number, Null for an error.
  If TypeOf json Is VBA.Collection _
  Then

    ' Reference for the top level JSON Array of the message.
    '  Strictly, you don't need to do this, but it does gives you intellisense.
    Set arry = json

    ' Loop through the JSON Array.
    For Each item In arry

      ' In VBA we cannot make the following TypeOf dict.Item("securitiesAccount") Is Scripting.Dictionary
      '  part of the enclosing If statement's conditional because VBA does not perform short
      '  circuit evaluation of conditionals.
      ' See Wikipedia article: https://en.wikipedia.org/wiki/Short-circuit_evaluation
      '
      ' In addition, when using the Scripting.Dictionary object you must test for the presence of the key
      '  **before** using the key, otherwise the Scripting.Dictionary object will **add** the key if it
      '  is not in the dictionary.
      ' See Remarks section: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/item-property-dictionary-object
      '
      If TypeOf item Is Scripting.Dictionary _
      Then

        ' Reference for a JSON Object in the top level JSON Array.
        '  Strictly, you don't need to do this, but it does gives you intellisense.
        Set dict = item

        ' Insure the "securitiesAccount" property exists in the message.
        If dict.Exists("securitiesAccount") Then

          Set item = dict.item("securitiesAccount")

          ' In VBA we cannot make the following TypeOf dict.Item("accountId") Is Scripting.Dictionary
          '  part of the enclosing If statement's conditional because VBA does not perform short
          '  circuit evaluation of conditionals.
          ' See Wikipedia article: https://en.wikipedia.org/wiki/Short-circuit_evaluation
          '
          ' In addition, when using the Scripting.Dictionary object you must test for the presence of the key
          '  **before** using the key, otherwise the Scripting.Dictionary object will **add** the key if it
          '  is not in the dictionary.
          ' See Remarks section: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/item-property-dictionary-object
          '
          If TypeOf item Is Scripting.Dictionary _
          Then

            ' Reference for a JSON Object securitiesAccount.
            '  Strictly, you don't need to do this, but it does gives you intellisense.
            Set acct = item

            ' Insure the "accountId" property exists in the message.
            If acct.Exists("accountId") _
            Then
              Debug.Print "accountId = " & acct.item("accountId")
            End If

          End If

        End If

      End If

    Next item

  End If

  Exit Sub
End Sub

Results in the following being printed to the immediate window of the VBA project editor:

accountId = 123
accountId = 456

[moeharoon]

Andrew, thank you for your quick response. This is what I really needed to understand for further coding. These APIs are coming from TDAmeritrade, I have little influence on how they are curated. The code above with the comment really helps put it in context

[houghtonap]

FYI, I'm not sure what your workflow is, how large the JSON document are, and what version of Office you are using, but if you are using Excel and VBA to handle this data you might also be interested in investigating using either PowerQuery in Excel or PowerBI Desktop. They both have JSON document support that you can massage into a table and may handle JSON documents that are larger than what VBA-JSON can. Just in case you run into any overall design issues for what you are trying to accomplish.

[cpajonk]

Sweet code. For a further improvement: The suggested code would benefit from some programming best practices like early returns, resulting in less nesting and code spaghetti. Formatting is questionable as well - but maybe the payment is on lines of code, then it's understandable.

After all it works already, that's the more important part.