Use RegEx to Pull Values from a JSON Response Where there is a special character in the regex-CodePudding

I am trying to further my VBA skillset by automating some reporting I am responsible for, but I am getting a little stuck and after a couple of weeks of trying and asking co-workers, I haven't been able to find a way to move forward. I've tried to sterilize the JSON as little as possible, but enough so i don't get fired, haha. Please let me know if any additional information is needed.

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]}]}]}

Here's 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've tried using some other solutions I've found here, but whenever I pass strResult as the string, and JSON3 as the pattern, the functions return an empty set.

I have also tried using VBA-JSON to parse the JSON into an object that I could extract the data from, but I am getting zero values from that no matter what I try there either. I followed the ReadMe, but am not sure how to pull the JSON from a variable, as the readme example pulls it from a file.

In a perfect world, I would like to be able 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.

Thanks for reading through my long-winded explanation. I apologize if this is a lot to ask, I feel like I'm close to where I want to be, but I could be very wrong too. Again, if any additional information is needed, I'll try my best to share it as soon as I can if possible. Thanks in advance.

CodePudding user response：

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.

[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)

CodePudding user response：

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
    
    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")
            DumpCollection data("values")
        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