I've done this using Python scripts on a Linux server. I believe you can run Python on Windows as well. The beauty of Python is has lots of pre-built plug in to handle parsing of JSON and CSV.
Basic scheme is :
Develop test python script that requests a hard coded URL and converts response into an AwareIM import friendly CSV
Update that script with <<BoName.AttributeName>> tags to inject the variable parts of your request(eg URL & Query) and use this to create a new AwareiM document template
Create a process that create a temporary Python script based on the document template (with variable parts injected) and then imports the resulting CSV into AwareIM
Delete the temporary Python script
Here is a script I use in product to get some free data from a web service. It actually provide a dump of data of all federal electorates in Australia. Typically you would make a more precise query and inject some data into the script at line geojson_url = 'http://mapit.openlocal.org.au/area/'+str(electorate_data[e]["id"])+'.geojson?simplify_tolerance=0.0001'
# ElectorateFederal_OpenLocal_Refresh.py
# This python script makes calls to MapIt.OpenLocal.org.au to import the OpenLocal ID and GeoJSON into all ElectorateFederal
# There is another similar script for ElectorateFederal
#
# Calls to MapIt.OpenLocal.org.au gets
# Name
# OpenLocalID
# GeoJSON
#
# Results are converted into a CSV file for import into the SSTWUA ElectorateFederal BO
#
# Because GeoJSON files get large we store these in the file system at <<SystemSettings.ServerCustomPath>>/GeoJSON/
# Which maps to <<SystemSettings.ServerCustomURL>>/GeoJSON/
#
# This script is called by an AwareIM process...it should not need to be called often.
# Setup to run nightly to pick up any changes in electorate date
#
import requests
import json
import os
import time
headers = {
'Content-Type': 'application/json',
}
# define input
electorate_url = 'http://mapit.openlocal.org.au/areas/CED'
# prepare quality output CSV file
csv_data = open("/tmp/ElectorateFederal_OpenLocal_Refresh.csv", "w", 0)
csv_data.write('*ElectorateFederal.Name,ElectorateFederal.OpenLocalID'+os.linesep)
# make electorate request
electorate = requests.post(electorate_url, headers=headers)
electorate_data = json.loads(electorate.text)
for e in electorate_data.keys():
geojson_url = 'http://mapit.openlocal.org.au/area/'+str(electorate_data[e]["id"])+'.geojson?simplify_tolerance=0.0001'
geojson = requests.post(geojson_url, headers=headers)
csv_data.write('"'+str(electorate_data[e]["name"]).title()+'","'+str(electorate_data[e]["id"])+'"'+os.linesep)
geojson_data = open("<<SystemSettings.ServerCustomPath>>/GeoJSON/"+str(electorate_data[e]["id"])+".geojson", "w", 0)
geojson_data.write('var electorate'+str(electorate_data[e]["id"])+' = '+str(geojson.text))
geojson_data.close()
# pause 2 seconds so localopen.org.au does not throttle us!
time.sleep(2)
csv_data.close();
