Pull values from a JSON response where there is a special character in the RegEx

Question

I am trying to automate reporting. I tried to sterilize the JSON as little as possible.

I am getting a JSON response from an API endpoint, and would like to extract the values sections of the responses into a string variable.

This is the JSON response (stored in variable strResult): {"totalCount":3,"nextPageKey":null,"resolution":"4h","result":[{"metricId":"builtin:service.keyRequest.count.total:names","dataPointCountRatio":3.6E-6,"dimensionCountRatio":3.0E-5,"data":[{"dimensions":["IMPORTANT_ARL_#1","SERVICE_METHOD-HEX#1"],"dimensionMap":{"dt.entity.service_method":"SERVICE_METHOD-HEX#1","dt.entity.service_method.name":"IMPORTANT_ARL_#1"},"timestamps":[1667289600000,1667304000000,1667318400000,1667332800000,1667347200000,1667361600000],"values":[null,1,30,26,null,null]},{"dimensions":["IMPORTANT_ARL_#2","SERVICE_METHOD-HEX#2"],"dimensionMap":{"dt.entity.service_method":"SERVICE_METHOD-HEX#2","dt.entity.service_method.name":"IMPORTANT_ARL_#2"},"timestamps":[1667289600000,1667304000000,1667318400000,1667332800000,1667347200000,1667361600000],"values":[60,371,1764,1964,1707,1036]},{"dimensions":["IMPORTANT_ARL_#3","SERVICE_METHOD-HEX#3"],"dimensionMap":{"dt.entity.service_method":"SERVICE_METHOD-HEX#3","dt.entity.service_method.name":"IMPORTANT_ARL_#3"},"timestamps":[1667289600000,1667304000000,1667318400000,1667332800000,1667347200000,1667361600000],"values":[9,6,1077,1171,462,null]}]}]}

The RegEx I wrote using regex101.com (?<=values\"\:\[)(.+?)(?=\])

I realize the double-quote is a problem, so I have a string variable (JSON3) set to this string with Chr(34) replacing the double quote.

JSON3 = "(?<=values\" & Chr(34) & "\:\[)(.+?)(?=\])"

debug.print json3 will show the correct string needed.

I tried solutions I found here, but whenever I pass strResult as the string, and JSON3 as the pattern, the functions return an empty set.

I also tried VBA-JSON to parse the JSON into an object that I could extract the data from, but I am getting zero values from that. I followed the ReadMe, but I want to pull the JSON from a variable while the ReadMe example pulls it from a file.

In a perfect world, I would like to store all of the keys/values in an object that I can extract the data from. This JSON is just one of many queries I am submitting to the API, so it would be ideal to have something that could be scaled for other uses as well.

Take a look at [this answer on parsing JSON](https://stackoverflow.com/a/46245469/4717755) and it may help. — PeterT, Nov 03 '22 at 20:33
`Set Parsed = JsonConverter.ParseJson(strResult)` would be how you'd get your json string parsed by VBA-JSON. https://github.com/VBA-tools/VBA-JSON#:~:text=%27%20Parse%20json%20to%20Dictionary%0A%27%20%22values%22%20is%20parsed%20as%20Collection%0A%27%20each%20item%20in%20%22values%22%20is%20parsed%20as%20Dictionary%0ASet%20Parsed%20%3D%20JsonConverter.ParseJson(JsonText) — Tim Williams, Nov 03 '22 at 21:27
Look for a JSON parser which supports JSON Path or similar DSL for fetching and updating nested values. Parsing JSON format is quite easy but its the access to parsed results which most VBA solutions suck at. Google for **mdJson.bas** for a better one. — wqw, Nov 05 '22 at 07:37
Thank you PeterT, the JsonEditorOnline.org is a fantastic resource that has helped me begin to understand how JSON is parsed into an object. I am still learning, and getting familiar with the structure, but definitely see the value in this tool. I have 100% added this to my bookmarks and will be referencing it frequently. — BaseTwo, Nov 08 '22 at 16:52

Lundt · Answer 1 · 2022-11-03T21:32:32.890

strResult = "{""totalCount"":3,""nextPageKey"":null,""resolution"":""4h"",""result"":[{""metricId"":""builtin:service.keyRequest.count.total:names"",""dataPointCountRatio"":3.6E-6,""dimensionCountRatio"":3.0E-5,""data"":[{""dimensions"":[""IMPORTANT_ARL_#1"",""SERVICE_METHOD-HEX#1""],""dimensionMap"":{""dt.entity.service_method"":""SERVICE_METHOD-HEX#1"",""dt.entity.service_method.name"":""IMPORTANT_ARL_#1""},""timestamps"":[1667289600000,1667304000000,1667318400000,1667332800000,1667347200000,1667361600000],""values"":[null,1,30,26,null,null]},{""dimensions"":[""IMPORTANT_ARL_#2"",""SERVICE_METHOD-HEX#2""],""dimensionMap"":{""dt.entity.service_method"":""SERVICE_METHOD-HEX#2"",""dt.entity.service_method.name"":""IMPORTANT_ARL_#2""},""timestamps"":[1667289600000,1667304000000,1667318400000,1667332800000,1667347200000,1667361600000],""values"":[60,371,1764,1964,1707,1036]},{""dimensions"":[""IMPORTANT_ARL_#3"",""SERVICE_METHOD-HEX#3""],""dimensionMap"":{""dt.entity.service_method"":""SERVICE_METHOD-HEX#3"",""dt.entity.service_method.name"":""IMPORTANT_ARL_#3""},""timestamps"":[1667289600000,1667304000000,1667318400000,1667332800000,1667347200000,1667361600000],""values"":[9,6,1077,1171,462,null]}]}]}"


A = Split(strResult, """values"":")
For Each thingy in A
    Msgbox thingy
Next

This makes it a smaller problem.

EG

[null,1,30,26,null,null]},{"dimensions":["IMPORTANT_ARL_#2","SERVICE_METHOD-HEX#2"],"dimensionMap":{"dt.entity.service_method":"SERVICE_METHOD-HEX#2","dt.entity.service_method.name":"IMPORTANT_ARL_#2"},"timestamps":[1667289600000,1667304000000,1667318400000,1667332800000,1667347200000,1667361600000],

EG To get totalcount

B = Split(strResult, "{""totalCount"":")
C = Split(B(1), ",")
Msgbox "Count is " & C(0)

Tim Williams · Accepted Answer · 2022-11-14T17:50:58.920

An example using VBA-JSON


'Import the VBA-JSON "JsonConverter.bas" file, and add a VBA reference to 
'  the Microsoft Scripting Runtime library
Sub Tester()
    
    Dim strResult, jobj, results As Object, itm As Object, data As Object
    Dim vals as Object
    
    strResult = ActiveSheet.Range("A1").Value 'read json from a cell
    
    Set jobj = JsonConverter.ParseJson(strResult)
    
    Set results = jobj("result") ' a Collection of dictionary objects
    For Each itm In results
        For Each data In itm("data")
            Set vals = data("values")
            DumpCollection vals
            Debug.Print "Total = ", SumNumeric(vals)
        Next data
    Next itm
    
End Sub

'print the contents of a Collection (assumes contents are printable...)
Sub DumpCollection(col As Collection)
    Dim itm
    Debug.Print "------------------"
    For Each itm In col
        Debug.Print itm
    Next itm
End Sub

'sum numeric items in a collection
Function SumNumeric(col As Collection) As Double
    Dim itm, tot as Double
    For Each itm In col
        If IsNumeric(itm) Then tot = tot + itm
    Next itm
    SumNumeric = tot
End Function

Thank you Tim, I had tried 'Set Parsed = JsonConverter.ParseJson(strResult)', but i don't think I understood the structure of how json gets parsed to properly set it up in an object. I'm still not entirely sure i follow what's going on, but this does work, and prints the different sets of numbers into the immediate window. thank you, I think this gets me to the next step that I was struggling with. — BaseTwo, Nov 08 '22 at 16:47
i'm still having some issues with this, but is there a way to total each 'itm' iteration (with 'Null' values set to '0') and store that value as a unique variable? — BaseTwo, Nov 14 '22 at 17:42

Pull values from a JSON response where there is a special character in the RegEx

2 Answers2