Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Get specific json value

I am creating a quick VBA script to decipher a json response. This is a work laptop so can’t install any other extensions.

Via API I get a JSON responds. So far all works. But how do I only list the responds for src id 1 (or other):. Desired return would be: "CHEMBL544428"

[{"src_id": "7", "src_compound_id": "31843"}, {"src_id": "1", "src_compound_id": "CHEMBL544428"}, {"src_id": "10", "src_compound_id": "591737"}, {"src_id": "39", "src_compound_id": "CB4389789"}, {"src_id": "22", "src_compound_id": "68551"}, {"src_id": "22", "src_compound_id": "657192"}, {"src_id": "49", "src_compound_id": "PD000053"}, {"src_id": "21", "src_compound_id": "14776141"}, {"src_id": "15", "src_compound_id": "SCHEMBL340474"}, {"src_id": "20", "src_compound_id": "Mianserin-hydrochloride"}, {"src_id": "26", "src_compound_id": "21535-47-7"}, {"src_id": "28", "src_compound_id": "MolPort-003-666-778"}, {"src_id": "31", "src_compound_id": "31005"}, {"src_id": "48", "src_compound_id": "HY-B0188A"}, {"src_id": "14", "src_compound_id": "2X03TN217S"}]

My current code to return the full json:

Function GetCmpd(inchi_key As String) As String

'    inchi_Key = "YNPFMWCWRVTGKJ-UHFFFAOYSA-N"

    Dim url as String 'define the API call
    url = "https://www.ebi.ac.uk/unichem/rest/inchikey/" & inchi_key
    On Error GoTo ErrorHandler

'   Dim funtionAccess As Object
    functionAccess = createUnoService("com.sun.star.sheet.FunctionAccess")

    'return the JSON data
    Cmpd_data = functionAccess.callFunction("WEBSERVICE",Array(url))

    GetCmpd = Cmpd_data
    Exit Function

    ErrorHandler:
        GetCmpd = "Error " & Err
        

End Function
Tags:

2

Answers


  1. 0

    There are two ways

    1. There might be some way to control the call to the webservice. That is something we can’t understand from the code you posted, but it isn’t unreasonable that it provides some filtering mechanism.
    2. Filter the response in your own code. The JSON response is a string and you can manipulate it as such. Normally, this is a stupid idea to do on your own and you should use something like VBA JSON. This is not an extension and you can add it to your existing Excel project. Without this, you will have to do roughly the same using functions like InStr, Mid, Left and Right. RegEx might also be an option.

    In short – go for VBA JSON

    Login or Signup to reply.
  2. 0
    • You don’t install any extensions. VBA JSON is bas file, you just need to import the module or copy its code.

    VBA-tools / VBA-JSON

    • In VBE add reference : Microsoft Scripting Runtime

    enter image description here

    • Following code read JOSN from cell A1, modify as needed.
    Sub Demo()
    Dim Json As Object
    Set Json = JsonConverter.ParseJson([a1])
    Debug.Print getSrcId(Json, "1")
End Sub

Function getSrcId(oJson As Object, ByVal src_id As String) As String
    Dim oDic
    For Each oDic In oJson
        If oDic("src_id") = src_id Then
            getSrcId = oDic("src_compound_id")
            Exit For
        End If
    Next
End Function
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search