Access Data via Single String Command

[sombies]

Is there a function built in to access data dynamically via a single string? For example, I am accessing the data below: MsgBox jsonResult("features")(1)("attributes")("ASSET_ID")

But I want to be able to request the same data with a dynamic string, like: MsgBox JsonString(jsonResult, "('features')(1)('attributes')('ASSET_ID')")

Does a function like this already exist?

[sombies]

I threw together a quick function that did what I need. But if there is an official function for this, I would be interested!

MsgBox JsonString(jsonResult, "('features')(1)('attributes')('ASSET_ID')")

Function JsonString(jsonTxt As Object, requestTxt As String) As String
    Dim strEnd As Boolean, strVal As String
    strEnd = False
    Do While Not strEnd
        If InStr(requestTxt, ")(") = 0 Then
            strEnd = True
        End If

        If InStr(requestTxt, "(") = InStr(requestTxt, "('") Then
            strVal = Mid(requestTxt, 3, InStr(requestTxt, ")") - 4)
            If strEnd Then
                JsonString = jsonTxt(strVal)
            Else
                Set jsonTxt = jsonTxt(strVal)
            End If
        Else
            strVal = Mid(requestTxt, 2, InStr(requestTxt, ")") - 2)
            If strEnd Then
                JsonString = jsonTxt(CInt(strVal))
            Else
                Set jsonTxt = jsonTxt(CInt(strVal))
            End If
        End If

        If Not strEnd Then requestTxt = Mid(requestTxt, InStr(requestTxt, ")") + 1)
    Loop
End Function

[Deedolith]

In other words, you want an accessor with a "path" argument. Something like XPath ?

[sombies]

In other words, you want an accessor with a "path" argument. Something like XPath ?

That might be exactly what I am looking for! Is there an example of using XPath (or something similar) with this project that I could reference? I originally was using another project that supported it natively in their project, but it had other compatibility issues: https://medium.com/swlh/excel-vba-parse-json-easily-c2213f4d8e7a

[houghtonap]

@sombies thanks for the pointer to that article. A nice simple implementation of a JSON parser in VBA. I would also like to point out for those people using Excel 2016 or later you can use Excel's built-in JSON parser, which is found in Power Query, and write a Power Query to produce a table in Excel. Using the example JSON from the article @sombies mentioned:

{
 "data" : {
   "receipt_time" : "2018-09-28T10:00:00.000Z",
   "site" : "Los Angeles",
   "measures" : [ {
      "test_id" : "C23_PV",
      "metrics" : [ {
          "val1" : [ 0.76, 0.75, 0.71 ],
          "temp" : [ 0, 2, 5 ],
          "TS" : [ 1538128801336, 1538128810408, 1538128818420 ]
        } ]
     }, 
  {
          "test_id" : "HBI2_XX",
          "metrics" : [ {
          "val1" : [ 0.65, 0.71 ],
          "temp" : [ 1, -7],
          "TS" : [ 1538128828433, 1538128834541 ]
          } ]
     }]
  }
}

The following Power Query will transform that JSON into an Excel table:

let

    location = "U:\Workbooks\Examples\json-example.txt",
    contents = File.Contents( location ),
    document = Json.Document( contents, TextEncoding.Utf8 ),

    test = Table.FromRecords( { document[data] } ),

    measures = Table.ExpandListColumn( test, "measures" ),
    measurement = Table.ExpandRecordColumn( measures, "measures", { "test_id", "metrics" } ),

    metrics = Table.ExpandListColumn( measurement, "metrics" ),

    metric = Table.ExpandRecordColumn( metrics, "metrics", { "val1", "temp", "TS" } ),
    metric.val1 = Table.ExpandListColumn( metric, "val1" ),
    metric.temp = Table.ExpandListColumn( metric.val1, "temp" ),
    metric.TS   = Table.ExpandListColumn( metric.temp, "TS" ),

    chg.types = Table.TransformColumnTypes
    (
        metric.TS,
        {
            { "receipt_time", type datetimezone },
            { "site",         type text         },
            { "test_id",      type text         },
            { "val1",         type number       },
            { "temp",         type number       },
            { "TS",           type number       }
        }
    ),

    result = chg.types

in result

The Excel table will look like:	receipt_time	site	test_id	val1	temp	TS
09/28/2018 10:00:00	Los Angeles	C23_PV	0.76	0	1538128801336
09/28/2018 10:00:00	Los Angeles	C23_PV	0.76	0	1538128810408
09/28/2018 10:00:00	Los Angeles	C23_PV	0.76	0	1538128818420
09/28/2018 10:00:00	Los Angeles	C23_PV	0.76	2	1538128801336
09/28/2018 10:00:00	Los Angeles	C23_PV	0.76	2	1538128810408
09/28/2018 10:00:00	Los Angeles	C23_PV	0.76	2	1538128818420
09/28/2018 10:00:00	Los Angeles	C23_PV	0.76	5	1538128801336
09/28/2018 10:00:00	Los Angeles	C23_PV	0.76	5	1538128810408
09/28/2018 10:00:00	Los Angeles	C23_PV	0.76	5	1538128818420
09/28/2018 10:00:00	Los Angeles	C23_PV	0.75	0	1538128801336
09/28/2018 10:00:00	Los Angeles	C23_PV	0.75	0	1538128810408
09/28/2018 10:00:00	Los Angeles	C23_PV	0.75	0	1538128818420
09/28/2018 10:00:00	Los Angeles	C23_PV	0.75	2	1538128801336
09/28/2018 10:00:00	Los Angeles	C23_PV	0.75	2	1538128810408
09/28/2018 10:00:00	Los Angeles	C23_PV	0.75	2	1538128818420
09/28/2018 10:00:00	Los Angeles	C23_PV	0.75	5	1538128801336
09/28/2018 10:00:00	Los Angeles	C23_PV	0.75	5	1538128810408
09/28/2018 10:00:00	Los Angeles	C23_PV	0.75	5	1538128818420
09/28/2018 10:00:00	Los Angeles	C23_PV	0.71	0	1538128801336
09/28/2018 10:00:00	Los Angeles	C23_PV	0.71	0	1538128810408
09/28/2018 10:00:00	Los Angeles	C23_PV	0.71	0	1538128818420
09/28/2018 10:00:00	Los Angeles	C23_PV	0.71	2	1538128801336
09/28/2018 10:00:00	Los Angeles	C23_PV	0.71	2	1538128810408
09/28/2018 10:00:00	Los Angeles	C23_PV	0.71	2	1538128818420
09/28/2018 10:00:00	Los Angeles	C23_PV	0.71	5	1538128801336
09/28/2018 10:00:00	Los Angeles	C23_PV	0.71	5	1538128810408
09/28/2018 10:00:00	Los Angeles	C23_PV	0.71	5	1538128818420
09/28/2018 10:00:00	Los Angeles	HBI2_XX	0.65	1	1538128828433
09/28/2018 10:00:00	Los Angeles	HBI2_XX	0.65	1	1538128834541
09/28/2018 10:00:00	Los Angeles	HBI2_XX	0.65	-7	1538128828433
09/28/2018 10:00:00	Los Angeles	HBI2_XX	0.65	-7	1538128834541
09/28/2018 10:00:00	Los Angeles	HBI2_XX	0.71	1	1538128828433
09/28/2018 10:00:00	Los Angeles	HBI2_XX	0.71	1	1538128834541
09/28/2018 10:00:00	Los Angeles	HBI2_XX	0.71	-7	1538128828433
09/28/2018 10:00:00	Los Angeles	HBI2_XX	0.71	-7	1538128834541

[houghtonap]

@sombies, a comment on the function you wrote. You could use a VBA ParamArray instead of a string to specify the path elements to the value you want. Additionally, I would suggest returning a Variant so number values are returned as numbers and not strings. Lastly, I would consider renaming your function to JsonValue since it could return elements of a Json Array or properties of a Json Object. For example the proposed changes would look like:

MsgBox JsonValue(jsonResult, "features", 1, "attributes", "ASSET_ID")

Public Function JsonValue(json As Object, ParamArray path() As Variant) As Variant
End Function

Your implementation can also key off the fact that when the value of the path item is a string you are accessing a VBA Scripting.Dictionary object (Json Object) and when the value of the path item is a number you are accessing a VBA VBA.Collection object (Json Array).

One last thing about the first parameter of your function which is declared as an Object. Valid Json, according to the specification, is:

1. null
2. true
3. false
4. number
5. string
6. { } an object
7. [ ] an array

Declaring a VBA Object handles only the latter two. You might consider using a VBA Variant instead.

Json BNF

json
    element
element
    ws value ws
value
    object
    array
    string
    number
    "true"
    "false"
    "null"