CODEWITHSUNDEEP
jsonexcelvba
Crimsonarcher
Crimsonarcher

Reputation: 125

Extract data from json object

I have Json that looks like {"valid":"true","isinCode":null,"lastUpdateTime":"15-OCT-2012 14:18:56","tradedDate":"15OCT2012","data":[{"change":"16.75","lastPrice":"5,703.30"}

I've been able to extract this into a JSON object using a jsonlib.

How do i extract 'lastPrice' from this as its a nested json (data is another json object)?

Upvotes: 0

Views: 990

Answers (1)

SWa
SWa

Reputation: 4363

I use something like the below, it's quite specific to the JSON though, but light enough to re-jig everytime I need to parse new data:

Public Sub ReadJson()

    Const JsonString    As String = "{""valid"":""true"",""isinCode"":null,""lastUpdateTime"":""15-OCT-2012 14:18:56"",""tradedDate"":""15OCT2012"",""data"":[{""change"":""16.75"",""lastPrice"":""5,703.30""}]}"
    Dim objJson         As Object
    Dim objSE           As Object

    Set objSE = CreateObject("ScriptControl")

    With objSE
        .Language = "JScript"
        .AddCode "function getProp(jsonObj, propertyName) { return jsonObj[propertyName]; } "
        .AddCode "function getSubProp(jsonObj, pName, propertyName) { return jsonObj[pName][0][propertyName]; } "
    End With

    With objSE
        Set objJson = .Eval("(" + JsonString + ")")
        Debug.Print .Run("getProp", objJson, "valid")
        Debug.Print .Run("getProp", objJson, "isinCode")
        Debug.Print .Run("getProp", objJson, "lastUpdateTime")
        Debug.Print .Run("getProp", objJson, "tradedDate")
        Debug.Print .Run("getSubProp", objJson, "data", "change")
        Debug.Print .Run("getSubProp", objJson, "data", "lastPrice")
    End With

End Sub

Upvotes: 1

Related Questions