How to extract a value from this json ?

[zigune]

Hello,

I advanced well with VBA-Web by requesting a REST API returning me a Json. I managed to convert the WebResponse to an Object with the ParseJson function and now I don't know how to retrieve values from this json.

Here is my code :

Dim Response As WebResponse
Set Response = ClientInstance.Execute(Request)

Dim ResponseJson As Object
Set ResponseJson = ParseJson(Response.Content)

And here is the returned value with a webbrowser :

{
    "collection": {
        "version": "1.0",
        "href": "https://sample-url.com/api/search?item=66-71-7",
        "links": [{
                "href": "https://sample-url.com/api/",
                "rel": "home"
            }
        ],
        "items": [{
                "data": [{
                        "name": "name",
                        "prompt": "Name",
                        "value": "1,10-phenanthroline"
                    }, {
                        "name": "ecNumber",
                        "prompt": "EC Number",
                        "value": "200-629-2"
                    }, {
                        "name": "casNumber",
                        "prompt": "CAS Number",
                        "value": "66-71-7"
                    }, {
                        "name": "rmlId",
                        "prompt": "RML Id",
                        "value": "100.000.572"
                    }, {
                        "name": "indexNumber",
                        "prompt": "Index Number",
                        "value": "613-092-00-8"
                    }, {
                        "name": "classCategoryCodes",
                        "prompt": "Class&Category codes",
                        "array": ["7048", "7079", "7082"]
                    }, {
                        "name": "hazardStatements",
                        "prompt": "Hazard Statements",
                        "array": ["{\"code\":\"7294\",\"text\":\"H301\"}", "{\"code\":\"7325\",\"text\":\"H400\"}", "{\"code\":\"7326\",\"text\":\"H410\"}"]
                    }, {
                        "name": "labellingHazardStatements",
                        "prompt": "Labelling Hazard Statements",
                        "array": ["{\"code\":\"7294\",\"text\":\"H301\"}", "{}", "{\"code\":\"7326\",\"text\":\"H410\"}"]
                    }, {
                        "name": "specificConcentrationLimitsMFactors",
                        "prompt": "Specific Concentration Limits&M-factors",
                        "array": []
                    }, {
                        "name": "supplementaryHazardStatementCodes",
                        "prompt": "Labelling: Suppl. Hazard statement Code(s)",
                        "array": []
                    }, {
                        "name": "pictograms",
                        "prompt": "Pictograms",
                        "array": ["{\"code\":\"GHS09\",\"text\":\"GHS09: environment\",\"type\":\"Picto\"}", "{\"code\":\"GHS06\",\"text\":\"GHS06: skull and crossbones\",\"type\":\"Picto\"}", "{\"code\":\"7086\",\"text\":\"Danger\",\"type\":\"Word\"}"]
                    }, {
                        "name": "notes",
                        "prompt": "Notes",
                        "array": []
                    }, {
                        "name": "urlToCLInventoryPage",
                        "prompt": "URL to C&L inventory page",
                        "value": "https://echa.europa.eu/information-on-chemicals/cl-inventory-database/-/discli/details/75977"
                    }
                ]
            }
        ],
        "queries": [{
                "href": "https://sample-url.com/api/search",
                "rel": "search",
                "prompt": "Search",
                "data": [{
                        "name": "q",
                        "prompt": "Search",
                        "value": ""
                    }
                ]
            }, {
                "href": "https://sample-url.com/api/search",
                "rel": "search",
                "prompt": "Search",
                "data": [{
                        "name": "name",
                        "prompt": "Name",
                        "value": ""
                    }, {
                        "name": "casNumber",
                        "prompt": "CAS Number",
                        "value": ""
                    }, {
                        "name": "ecNumber",
                        "prompt": "EC Number",
                        "value": ""
                    }, {
                        "name": "indexNumber",
                        "prompt": "Index Number",
                        "value": ""
                    }
                ]
            }
        ]
    }
}

My question is how can I retrive the "hazardStatements" who are "H301", "H400" and "H410" ?

Can somebody help me ?

Thank you in advance for your help and best regards.

zigune

[zigune]

I close it as it's not an issue but a help request...

[aholden10]

I would spend some time analyzing the key paths at this site:https://www.convertjson.com/json-path-list.htm

The JSON you are receiving seems very complex!

On Wednesday, December 16, 2020, 01:32:00 AM EST, zigune <notifications@github.com> wrote:  

I close it as it's not an issue but a help request...

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or unsubscribe.