VBA-json

[ericlch16]

Hello experts,

I am new to JSON and i learned how to connect and GET data in json string variable via VBA. All good.

But i have a question. In SQL, you can retrieve one record based to an argument e.g ‘select * from xxxtable where id=1234’. In JSON , how to do something similar i.e retrieve one object based on an id only, in this case 1234. Do i pass that id when i connect to the API ? I googled it but i am confused and this forum is the best for answer.

 

[Rijnsent]

Hi Eric, do you have some code you can share? When I work with JSON in VBA I generally use this bit of code: GitHub - VBA-tools/VBA-JSON: JSON conversion and parsing for VBA , as it makes it much easier to do something with the JSON. But what you need to send to that API probably is somewhere in the API description/manual.
This is a project I've built that uses quite a lot of GET, APIs and JSON, maybe it inspires/helps you a bit: GitHub - krijnsent/crypto_vba: An Excel/VBA project to communicate with various cryptocurrency exchanges APIs (not maintained anymore)

 

[ericlch16]

Hi Eric, do you have some code you can share? When I work with JSON in VBA I generally use this bit of code: GitHub - VBA-tools/VBA-JSON: JSON conversion and parsing for VBA , as it makes it much easier to do something with the JSON. But what you need to send to that API probably is somewhere in the API description/manual.
This is a project I've built that uses quite a lot of GET, APIs and JSON, maybe it inspires/helps you a bit: GitHub - krijnsent/crypto_vba: An Excel/VBA project to communicate with various cryptocurrency exchanges APIs (not maintained anymore)

Yes i am already using GitHub library.

When i did database long time ago, i used

“Select name in mytable where id =15” and I will get only 1 record instead of all the names in the table.

So my question was : How do i achieve that in json query to an api? I am guessing it in in the url with parameters:

Https\\myurl ?id=15

Is my assumption correct? I am a beginner in API and JSON

 

[Rijnsent]

Hi Eric,

it depends a bit from the API... If it's a GET request, this can be a valid example (this one also works in your browser):
https://nominatim.openstreetmap.org/reverse?format=json&addressdetails=1&lat=52.1&lon=5.2 -> this basically translates a coordinate into an address. In the documentation ( Reverse - Nominatim Manual ) you can find how to build it up and which parameters you can add (in this case it all goes in one URL). Sometimes you need to include something in the header, sometimes something in the body, that really depends on how the API was set up.

In my code I'm normally using crypto_vba/ModWeb.bas at master · krijnsent/crypto_vba this as a basis. It's a bit more structured and I use the WebRequestURL function to execute the GET/POST/etc.

This site has quite a nice manual: Getting Data from a Website in JSON format using VBA - My Excel Genius

If you can share the API I could have a look .