Json.lookup

General
1

I have the following structure in a JSonItem:

{ "name": "Herbert Traunsteiner", "social_security_number": "0003 040404", "year": 2015, "quarter": 2, "deferred_payment_negotiated": false, "account_balance_overview": { "balance_from_previous_quarters": { "amount": -4110.19, "entries": [ { "label": "Saldovortrag vom 24.01.2015", "amount": -9731.68 }, { "label": "Geldleistungen", . . .

How can I extract this one item : “Saldovortrag vom 24.01.2015” ?

I tried with

wort=MyJsonItem.lookup("account_balance_overview.balance_from_previous_quarters.entries(0).label","Not found")

but get always “Not found” back

2

I think you need to create a JSONItem for “account_balance_overview” and then for “balance_from_previous_quarters”…

3

with which command?

4

This should get it done…

  Dim tmpItem As JSONItem
  
  tmpItem = MyJsonItem.Value("account_balance_overview")
  tmpItem = tmpItem.Value("balance_from_previous_quarters") 
  tmpItem = tmpItem.Value("entries") 
  tmpItem = tmpItem.Child(0)
  
  Msgbox tmpItem.Value("label")

I’m just wondering if there isn’t perhaps a better way to read the path?

5

Since this is something I also need often (looking up values with a given path), I’ve created an LookupPath extends method.

Simply copy the following method to a PUBLIC MODULE:

Function LookupPath(extends Item As JSONItem, Path As String, DefaultValue As Variant, Delimiter As String = ".") As Variant
  Dim tmpItem As Variant
  Dim pathArr() As String
  Dim i As Integer
  Dim openBracketPos As Integer
  Dim arrIndex As Integer
  Dim tmpStr As String
  
  pathArr = Path.Split(Delimiter)
  
  tmpItem = Item
  while (tmpItem <> nil) and (i <= pathArr.Ubound)
    
    if JSONItem(tmpItem).HasName(pathArr(i)) then
      
      tmpItem = JSONItem(tmpItem).Value(pathArr(i))
      i = i + 1
      
    else
      
      openBracketPos = Instr(0, pathArr(i), "(")
      if (openBracketPos > 0) and (Right(pathArr(i), 1) = ")") then
        arrIndex = Val(Mid(pathArr(i), openBracketPos + 1, Len(pathArr(i)) - (openBracketPos + 1) ))
        tmpStr = Left(pathArr(i), openBracketPos - 1)
        if JSONItem(tmpItem).HasName(tmpStr) then
          tmpItem = JSONItem(tmpItem).Value(tmpStr)
          if JSONItem(tmpItem).IsArray then
            if arrIndex < JSONItem(tmpItem).Count then
              tmpItem = JSONItem(tmpItem).Child(arrIndex)
              i = i + 1
            else
              tmpItem = nil
            end if
          else
            tmpItem = nil
          end if
        else
          tmpItem = nil
        end if
      else
        tmpItem = nil
      end if
      
    end if
    
  wend
  
  if tmpItem = nil then
    tmpItem = DefaultValue
  end if
  
  return tmpItem
  
End Function

Now you can lookup values using paths like follow on any JSONItem:

wort = MyJsonItem.LookupPath("account_balance_overview.balance_from_previous_quarters.entries(0).label","Not found")
1 Like
6

thx a lot