If the JSON string returned from the RESTFul Web Service perfectly meets the requirements specified in the section called “JSON formats (required by RESTClient object)” in PowerScript Reference, you can directly use the RESTClient object to get the data, as shown below.
restclient lnv_restclient
string ls_url
long ll_row
lnv_restclient = create restclient
ls_url = "https://rest.appeon.test/getemployees"
/* JSON string retruned from the url
[
{"Id":1106,"First_name":"Vincent","Last_name":"Phillipino","Sex":"Male","Age":63},
{"Id":1107,"First_name":"Natalie","Last_name":"Mariano","Sex":"Female","Age":16},
{"Id":1108,"First_name":"Li","Last_name":"Mary","Sex":"Female","Age":36},
{"Id":1109,"First_name":"Vic","Last_name":"Lu","Sex":"male","Age":20}
]*/
ll_row = lnv_restclient.retrieve(dw_1,ls_url)
destroy lnv_restclient
messagebox("Restclient","The rowcount of dw_1 = "+string(ll_row))
If the JSON string returned from the RESTFul Web Service partially meets the requirement, you may consider using the JSONPackage object to get the part of JSON string that meet the requirement, and then use DataWindow ImportJson or ImportJsonByKey function to import the JSON data to the DataWindow.
string ls_value
string ls_url
string ls_json
long ll_return,ll_row
httpclient lnv_httpclient
jsonpackage lnv_pack1,lnv_pack2
lnv_pack1 = create jsonpackage
lnv_pack2 = create jsonpackage
lnv_httpclient = create httpclient
//Get the JSON string via httpclient
ls_url = "https://test.appeon.com/getfood"
ll_return = lnv_httpclient.sendrequest("Get",ls_url)
if ll_return <> 1 then
messagebox("Failed","SendRequest Failed:"+lnv_httpclient.getresponsestatustext( ))
return
end if
lnv_httpclient.getresponsebody( ls_json)
/* JSON string returned from the URL：
'{
"id": "0001",
"type": "donut",
"name": "Cake",
"ppu": 0.55,
"batters":
{
"batter":
[
{ "id": "1001", "type": "Regular" },
{ "id": "1002", "type": "Chocolate" },
{ "id": "1003", "type": "Blueberry" },
{ "id": "1004", "type": "Devil~'s Food"},
{ "id": "5001", "type": "None" }
]
}
}'*/
//Load the JSON string via jsonpackage
lnv_pack1.loadstring(ls_json)
//Get the JSON string under key=batters
ls_value = lnv_pack1.getvalue("batters")
//Load the new JSON string via jsonpackage
lnv_pack2.loadstring( ls_value)
//Get the JSON data under key=batter (this json data meets the requirements by RestClient)
ls_value = lnv_pack2.getvalue( "batter")
//Import JSON data to the DataWindow via importjson
dw_1.importjson(ls_value)
destroy lnv_pack1
destroy lnv_pack2
destroy lnv_httpclient
If the JSON string returned from the RESTFul Web Service does not meet the requirement, for example, the top-level are not arrays, or item contains null values, you may consider using the JSONParser and HTTPClient objects to import the JSON data to the DataWindow.
string ls_json
string ls_url
string ls_error
long ll_row,ll_return
long ll_root,ll_object,ll_item
long ll_loop1,ll_loop2
long ll_id,ll_data
string ls_data,ls_key
jsonparser lnv_jsonparser
httpclient lnv_httpclient
lnv_httpclient = create httpclient
lnv_jsonparser = create jsonparser
ls_url = "https://json.appeon.test/employees"
//Get the JSON string via httpclient
ll_return = lnv_httpclient.sendrequest("Get",ls_url)
if ll_return <> 1 then
messagebox("Failed","SendRequest Failed:"+lnv_httpclient.getresponsestatustext( ))
return
end if
lnv_httpclient.getresponsebody( ls_json)
/* JSON string returned from the URL：
{
"1106":{"First_name":"Vincent","Last_name":"Phillipino","Sex":"Male","Age":63},
"1107":{"First_name":"Natalie","Last_name":"Mariano","Sex":"Female","Age":16},
"1108":{"First_name":"Li","Last_name":"Mary","Sex":"Female","Age":36},
"1109":{"First_name":"Vic","Last_name":null,"Sex":"male","Age":20}
}*/
//Loads the JSON data via jsonpaser
ls_error = lnv_jsonparser.loadstring(ls_json)
if len(trim(ls_error)) > 0 then
Messagebox("Failed","Load json failed:"+ls_error)
return
end if
//Obtains the handle of root item
ll_root = lnv_jsonparser.getrootitem( )
//Obtains the each row in a loop
for ll_loop1 = 1 to lnv_jsonparser.getchildcount(ll_root)
ll_row = dw_1.insertrow(0)
//Obtains ID
ll_id = long(lnv_jsonparser.getchildkey(ll_root, ll_loop1))
dw_1.setitem( ll_row,"id", ll_id)
//Obtains the other column data in a loop
ll_object = lnv_jsonparser.getchilditem( ll_root, ll_loop1)
for ll_loop2 = 1 to lnv_jsonparser.getchildcount( ll_object)
ll_item = lnv_jsonparser.getchilditem( ll_object, ll_loop2)
ls_key = lnv_jsonparser.getchildkey( ll_object, ll_loop2)
//Obtains the data type of each item
choose case lnv_jsonparser.getitemtype( ll_item)
case jsonarrayitem!,jsonobjectitem!,jsonnullitem!
//ignores array, object and null item
case jsonstringitem!
ls_data = lnv_jsonparser.getitemstring(ll_object,ls_key)
dw_1.setitem(ll_row,ls_key,ls_data)
case jsonnumberitem!
ll_data = lnv_jsonparser.getitemnumber(ll_object,ls_key)
dw_1.setitem(ll_row,ls_key,ll_data)
case jsonbooleanitem!
//handles boolean as string
ls_data = string(lnv_jsonparser.getitemboolean( ll_object,ls_key))
dw_1.setitem(ll_row,ls_key,ls_data)
end choose
next //Finish processing one row
next//Start processing next row
destroy lnv_jsonparser
