I do this all the time using VBA-JSON that someone else here mentioned.
Use recordsets to get the data you need. Anthing in {curly brackets} use a Dictionary, anything in [square brackets] use a Collection with dictionaries within it. Then using the convert to json function, pass the final dictionary (since the main body is within a dictionary) to the function. It works amazingly well!