Json Tests - final for now

[stanl]

Attached is a summary of tests I went through in the messing with Json thread. The script should return results, and in most cases a view, of the Json the script attempts to parse into Excel

Code (WINBATCH) Select Expand



testURLS = "BREW,ZIP,USPTO,NHTSA,ALERTS,STOCKS,STOCK_HIST"
ConfigUrl("STOCK_HIST")



just substitute one of the testURLS into ConfigUrl. The code is still clumsy but should work to test other API's. I added 'BREW' last night for beer lovers. ALERTS required a special flag to avoid trying to parse out keys/values - it gives a Jscript error - 'Conditional Compilation has been turned off' - and there doesn't seem to be a way around it. STOCK_HIST is actually delivered as .csv and STOCKS works but ugly.


At least you get a look at a variety of Json and the goal was to come up with as generic as possible a method to interpret and evaluate all sections w/out prior knowledge of the keys, subkeys and values.

Code (WINBATCH) Select Expand



;Winbatch 2020A - Tests for multiple API's and Json
;Data sent to Excel
;Stan Littlefield, October 11, 2020
;======================================================================================================
IntControl(73,1,0,0,0)
Gosub udfs
request=0
oS=0
cUrl=""
oWSName="Converted Json"
isCSV =0
bypass=0
cFile = 'c:\temp\Output.Json'
if FileExist(cFile) then FileDelete(cFile)


;testURLS are configurations to send an HTTP request
;in the ConfigUrl() UDF you can adjust to test specific parameters
;NOTE: changes to ALERTS and STOCK_HIST were needed to route
;the request through the script - 2 flags isCSV and bypass [above] used
testURLS = "BREW,ZIP,USPTO,NHTSA,ALERTS,STOCKS,STOCK_HIST"


ConfigUrl("STOCK_HIST")


ClipPut("")
ClipPut(cUrl)
Exit


request = Createobject("WinHttp.WinHttpRequest.5.1")
request.Open("GET", cUrl, @False )
If ! isCSV
   request.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded")
   request.SetRequestHeader("Accept", "application/json")
Endif
request.Send()
jdata = request.ResponseText
ObjectClose(request)


If isCSV
   BoxOpen("Parsing Raw Json To Excel","Please Wait....")
   ClipPut("")
   ClipPut(StrReplace(jdata,",",@TAB))
   toXL()
Endif




If Strsub(jdata,1,1) == "[" Then jdata = '{"Results":':jdata:'}'


If StrLen(StrTrim(jdata)) < 30 Then Terminate(@TRUE,"Exiting","No data returned")


;instantiate here and send as parameter to UDF's
oS = CreateObject ("MSScriptControl.ScriptControl")
oS.Language = "JScript"


Message("Raw Json",jdata) ;comment if you don't wish to see raw Json
BoxOpen("Parsing Raw Json To Excel","Please Wait....")


jkeys = Keys(oS,jdata)
;Message("Keys",jkeys) ;uncomment to see keys


If bypass then goto planB








n = ItemCount(jkeys,",")
For i = 1 To n-1
   k = ItemExtract(i,jkeys,",")
   skey =int(KeyLen(oS,jdata,k))
   If skey>0
      vals = rKeys(oS,jdata,k)
      If Strlen(vals)>2
          parsedData = StrReplace(vals,",",@TAB):@LF:props(oS,jdata,k,vals)
          FilePut(cFile, StrReplace (parsedData, @LF, @CRLF))
          If FileExist(cFile)
             ObjectClose(oS)
             ClipPut("")
             ClipPut(FileGet(cFile))
             toXL()
          Endif
      Endif
   Endif
Next


If ! FileExist(cFile) ;no array data found
   goto planB
Else
   goto endit
Endif




;uncomment below to see other displays
:planB
output = GetJSON1(oS,jdata) ; there is also GetJSON(oS,jdata)
ObjectClose(oS)
output = StrReplace(output,",",@TAB)  ;change to Tab-delimited
FilePut(cFile, StrReplace (output, @LF, @CRLF))
If FileExist(cFile)
   ClipPut("")
   ClipPut(FileGet(cFile))
   toXL()
Endif


:endit
Exit
;======================================================================================================
:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
;======================================================================================================


:udfs
#DefineSubRoutine err()
wberroradditionalinfo = wberrorarray[6]
lasterr = wberrorarray[0]
handlerline = wberrorarray[1]
textstring = wberrorarray[5]
linenumber = wberrorarray[8]
errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
Return(errmsg)
#EndSubRoutine




#DefineFunction GetJSON(oS,jdata)
IntControl(73,1,0,0,0)
code = $"
function json2txt(obj)
{
  var txt = '';
  var recurse = function(_obj) {
    if ('object' != typeof(_obj)) {
      txt += ',' + _obj + '\n';
    }
    else {
      for (var key in _obj) {
        if (_obj.hasOwnProperty(key)) {
          txt += key;
          recurse(_obj[key]);
        }
      }
    }
  };
  recurse(obj);
  return txt;
}
$"
oS.AddCode(:code)
Return oS.Eval(: `json2txt(` : jdata : `,'')`)


:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
#EndFunction


#DefineFunction GetJSON1(oS,jdata)
IntControl(73,1,0,0,0)
code = $"
function json2txt(obj,path){
   var txt='';
   for(var key in obj){
      if(obj.hasOwnProperty(key)){
         if('object'==typeof(obj[key])){
         txt += json2txt(obj[key],path+(path?''::'')+key);}
         else{txt+=path+','+key+','+obj[key]+'\n';}
      }
   }
   return txt;
}
$"
oS.AddCode(:code)
Return oS.Eval(: `json2txt(` : jdata : `,'')`)


:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())


#EndFunction




#DefineFunction Keys(oS,jdata)
oS.AddCode(: `function getKeys(obj,path){ var keys = ''; for (var i in obj) { keys += i + ','; } return keys; }`)
Return oS.Eval(: `getKeys(` : jdata : `,'')`)
#EndFunction


#DefineFunction rKeys(oS,jdata,r)
code= $"
function rKeys(obj,path){ var subkeys = '';
for (var j in obj.%r%[1]) {
subkeys += j + ','; }
return subkeys; }
$"
oS.AddCode(:code)
Return oS.Eval(: `rKeys(` : jdata : `,'')`)
#EndFunction


#DefineFunction KeyLen(oS,jdata,k)
IntControl(73,21,0,0,0)
oS.Reset()
code = "function klen(obj,path){ if (obj.%k% && obj.%k%.length) {return obj.%k%.length } }"
oS.AddCode(:code)
Return oS.Eval(: `klen(` : jdata : `,'')`)


:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
#EndFunction
                                           
#DefineFunction props(oS,jdata,k,vals)
v=ItemCount(vals,",")-1


code = $"
function getprops(obj,path) {
var x = ''; for (i = 0; i < obj.%k%.length; i++)
{ x +=
$"
For v1 = 1 to v
   code = code: "obj.%k%[i].":ItemExtract(v1,vals,","):" + '\t' + "
Next
code = code:"'\n' ; } return x }"
oS.AddCode(:code)
Return oS.Eval(: `getprops(` : jdata : `,'')`)
#EndFunction


#DefineFunction jsnObj(oS,jdata)
oS.Reset()
code = "function parse(obj,path) {var jsonObject = ":jdata:" }"
oS.AddCode(:code)
jobj = oS.CodeObject.jsonObject
Return oS.Eval(`(` : jobj : `)`)
#EndFunction


#DefineSubRoutine toXL()
IntControl(73,21,0,0,0)
oXL = CreateObject("Excel.Application")
oXL.Visible          = @TRUE  ; change this to @FALSE to run hidden
oXL.ScreenUpdating   = @TRUE  ; if running hidden, change this to @FALSE
oXL.UserControl      = @TRUE
oXL.DisplayAlerts    = @FALSE
oXL.WorkBooks.Add()
oWS = oXL.ActiveWorkBook.Worksheets(1)
oWS.Activate()
BoxShut()
oWS.Name = oWSName
oWS.Cells(1,1).Select()
oWS.Paste()
oWS.UsedRange.Select()
oXL.Selection.Font.Name = 'Tahoma'
oXL.Selection.Font.Size = 9
oXL.Selection.Font.Bold = @True
oWS.UsedRange.Columns.Autofit()
oWS.ListObjects.Add(:1,oWS.UsedRange, , 1).Name ="Table1"
oWS.Range("Table1[#All]").Select()
oWS.ListObjects("Table1").TableStyle = "TableStyleLight15"
oXL.ActiveWindow.DisplayGridlines = @False
oWS.Cells(1,1).Select()
oWS=0
oXL=0
Pause("Data Loaded Into Excel","Save or Close Workbook")
Exit
:WBERRORHANDLER
oWS=0
oXL=0
Terminate(@TRUE,"Error Encountered",err())
#EndSubRoutine


#DefineSubRoutine ConfigUrl(site)


If site=="BREW"
;qry="by_city=Louisville"
;qry="by_name=Amber"
;qry="by_state="North_Carolina"
;qry="by_postal=27603"
qry="by_type=brewpub" 
oWSName=StrReplace(qry,"=","_")
;[must be one of these micro,regional,brewpub,large,planning,bar,contract,proprietor]
cUrl = "https://api.openbrewerydb.org/breweries?":qry
Endif


If site=="ZIP"
zip = '27603'
cUrl = "https://api.targetlock.io/v1/post-code/":zip
oWSName="ZipCode Data"
Endif


If site=="USPTO"
cUrl = "https://developer.uspto.gov/ipmarketplace-api/search/query"
oWSName="Patents"
Endif


If site=="NHTSA"
main = "https://vpic.nhtsa.dot.gov/api/"
;sub =  "vehicles/GetModelsForMake/honda?format=json"
;sub =  "vehicles/GetMakesForManufacturerAndYear/mer?year=2013&format=json"
sub =  "vehicles/GetMakeForManufacturer/honda?format=json"
;sub =  "vehicles/GetAllMakes?format=json"
;sub =  "vehicles/DecodeVin/5UXWX7C5*BA?format=json&modelyear=2011"
;sub =  "vehicles/DecodeVinValues/5UXWX7C5*BA?format=json&modelyear=2011"
;sub =  "vehicles/DecodeWMI/1FD?format=json"
;sub =  "vehicles/GetWMIsForManufacturer/hon?format=json"
;sub =  "vehicles/GetManufacturerDetails/honda?format=json"
;sub =  "vehicles/GetVehicleVariableList?format=json"
cUrl = main:sub
oWSName="NHTSA_Vehicle_Query"
Endif


IF site=="ALERTS"
cUrl="https://api.weather.gov/alerts/active?area=NC"
oWSName="Weather Alerts"
bypass=1
Endif


If site=="STOCKS"
symbols =  "IBM,GOOG,AAPL"
cURL = "https://query1.finance.yahoo.com/v7/finance/quote?symbols=%symbols%"
oWSName="Stock Quotes"
Endif


If site=="STOCK_HIST"
;set up for weekly week period
base = "1970:01:01:00:00:00"
base = TimeSubtract(base,"0000:00:00:05:00:00") ;adjust for EST, but shouldn't matter
Now=TimeYmdHms()
Now1 = TimeSubtract(Now,"0000:00:350:00:00:00")
p2 = TimeDiffSecs(Now,base)
p1 = TimeDiffSecs(Now1,base)


index = "DAL"
cUrl = "https://query1.finance.yahoo.com/v7/finance/download/%index%?period1=%p1%&period2=%p2%&interval=1wk&events=history"
oWSName=index:"_Weekly"
isCSV=1
Endif


Return(cUrl)
#EndSubRoutine


Return
;======================================================================================================




[JTaylor]

I hesitate to ask due to the risk of my question being misconstrued but I am truly curious...did you find anything you were able to do with the Script Control that SQLite JSON capabilities would not handle?  I realize you may not have made a comparison but wanted to ask.

Jim

[stanl]

Fair question and I mentioned the Extender in the original post on the messing with Json thread. I'm positive the Extender could provide more precise output with SQL syntax, but that assumes prior knowledge of of keys. The point here is to discover the keys. If the SQLite Json addition can replicate something akin to OpenSchema() with a Json string that would be perfect.

[JTaylor]

Yeah...I just didn't want you to think I was trying to push SQLite over what you were doing.  I really was curious.   Thought it might give me some ideas for new functions.

Do you mean something like the attached?   Wasn't sure what format you wanted and if you wanted the entire structure or not.   Attached just pulls a distinct listing rather than repeating for every result row.   Your scripts all fail for me on the Excel stuff so couldn't see the end results.  Not sure why.  I am running Office365.

If you haven't, use something like DB Browser and open some JSON in a query there and you will see how it breaks everything down in the json_tree table.


Jim

[stanl]

Nice ;D  I see you didn't take on ALERTS, which has a JSON polygon. I have DB Browser for SQLite, explain how you to query Json?


BTW: [personal opinion] Office 365 sucks, just like Microsoft Teams.

[td]

BTW: [personal opinion] Office 365 sucks, just like Microsoft Teams.

I know at least one other person that shares your sentiments.

[JTaylor]

I missed ALERTS somehow.  One example below where you don't have a table.     You can also create a table and field and do something like the following (it is from something else not related to your question but demonstrates how) where json_tab is the table and jtxt is the field.

Code (winbatch) Select Expand


SQLText = "SELECT fullkey, value FROM json_tab, json_tree(json_tab.jtxt, '$.books[0]') WHERE json_tree.type NOT IN ('object','array');"


Code (winbatch) Select Expand



select * from json_tree('{
    "@context": [
        "https://geojson.org/geojson-ld/geojson-context.jsonld",
        {
            "@version": "1.1",
            "wx": "https://api.weather.gov/ontology#",
            "@vocab": "https://api.weather.gov/ontology#"
        }
    ],
    "type": "FeatureCollection",
    "features": [
        {
            "id": "https://api.weather.gov/alerts/NWS-IDP-PROD-4482430-3724329",
            "type": "Feature",
            "geometry": null,
            "properties": {
                "@id": "https://api.weather.gov/alerts/NWS-IDP-PROD-4482430-3724329",
                "@type": "wx:Alert",
                "id": "NWS-IDP-PROD-4482430-3724329",
                "areaDesc": "Inland New Hanover",
                "geocode": {
                    "UGC": [
                        "NCZ107"
                    ],
                    "SAME": [
                        "037129"
                    ]
                },
                "affectedZones": [
                    "https://api.weather.gov/zones/forecast/NCZ107"
                ],
                "references": [
                    {
                        "@id": "https://api.weather.gov/alerts/NWS-IDP-PROD-4481682-3723800",
                        "identifier": "NWS-IDP-PROD-4481682-3723800",
                        "sender": "w-nws.webmaster@noaa.gov",
                        "sent": "2020-10-13T03:46:00-04:00"
                    }
                ],
                "sent": "2020-10-13T15:26:00-04:00",
                "effective": "2020-10-13T15:26:00-04:00",
                "onset": "2020-10-13T18:00:00-04:00",
                "expires": "2020-10-13T21:00:00-04:00",
                "ends": "2020-10-13T21:00:00-04:00",
                "status": "Actual",
                "messageType": "Update",
                "category": "Met",
                "severity": "Minor",
                "certainty": "Likely",
                "urgency": "Expected",
                "event": "Coastal Flood Advisory",
                "sender": "w-nws.webmaster@noaa.gov",
                "senderName": "NWS Wilmington NC",
                "headline": "Coastal Flood Advisory issued October 13 at 3:26PM EDT until October 13 at 9:00PM EDT by NWS Wilmington NC",
                "description": "* WHAT...Up to one half foot of inundation above ground level\nexpected in low-lying areas near shorelines and tidal\nwaterways.\n\n* WHERE...The lower Cape Fear River including downtown\nWilmington.\n\n* WHEN...Until 9 PM EDT this evening.\n\n* IMPACTS...The lowest parts of USS North Carolina Road and low\nspots of Battleship Road begin to flood. Water begins to\nspread out of the storm drains onto Water Street just south of\nMarket Street in downtown Wilmington.",
                "instruction": "If travel is required, allow extra time as some roads may be\nclosed. Do not drive around barricades or through water of\nunknown depth. Take the necessary actions to protect flood-prone\nproperty.",
                "response": "Monitor",
                "parameters": {
                    "NWSheadline": [
                        "COASTAL FLOOD ADVISORY REMAINS IN EFFECT UNTIL 9 PM EDT THIS EVENING"
                    ],
                    "VTEC": [
                        "/O.CON.KILM.CF.Y.0095.201013T2200Z-201014T0100Z/"
                    ],
                    "PIL": [
                        "ILMCFWILM"
                    ],
                    "BLOCKCHANNEL": [
                        "CMAS",
                        "EAS",
                        "NWEM"
                    ],
                    "eventEndingTime": [
                        "2020-10-14T01:00:00+00:00"
                    ]
                }
            }
        },
        {
            "id": "https://api.weather.gov/alerts/NWS-IDP-PROD-4482187-3724128",
            "type": "Feature",
            "geometry": null,
            "properties": {
                "@id": "https://api.weather.gov/alerts/NWS-IDP-PROD-4482187-3724128",
                "@type": "wx:Alert",
                "id": "NWS-IDP-PROD-4482187-3724128",
                "areaDesc": "Northern Outer Banks; Hatteras Island",
                "geocode": {
                    "UGC": [
                        "NCZ203",
                        "NCZ205"
                    ],
                    "SAME": [
                        "037055"
                    ]
                },
                "affectedZones": [
                    "https://api.weather.gov/zones/forecast/NCZ203",
                    "https://api.weather.gov/zones/forecast/NCZ205"
                ],
                "references": [
                    {
                        "@id": "https://api.weather.gov/alerts/NWS-IDP-PROD-4481805-3723859",
                        "identifier": "NWS-IDP-PROD-4481805-3723859",
                        "sender": "w-nws.webmaster@noaa.gov",
                        "sent": "2020-10-13T04:48:00-04:00"
                    },
                    {
                        "@id": "https://api.weather.gov/alerts/NWS-IDP-PROD-4481337-3723617",
                        "identifier": "NWS-IDP-PROD-4481337-3723617",
                        "sender": "w-nws.webmaster@noaa.gov",
                        "sent": "2020-10-12T21:40:00-04:00"
                    },
                    {
                        "@id": "https://api.weather.gov/alerts/NWS-IDP-PROD-4481804-3723858",
                        "identifier": "NWS-IDP-PROD-4481804-3723858",
                        "sender": "w-nws.webmaster@noaa.gov",
                        "sent": "2020-10-13T04:48:00-04:00"
                    }
                ],
                "sent": "2020-10-13T11:06:00-04:00",
                "effective": "2020-10-13T11:06:00-04:00",
                "onset": "2020-10-13T11:06:00-04:00",
                "expires": "2020-10-13T20:00:00-04:00",
                "ends": "2020-10-13T20:00:00-04:00",
                "status": "Actual",
                "messageType": "Update",
                "category": "Met",
                "severity": "Moderate",
                "certainty": "Likely",
                "urgency": "Expected",
                "event": "Beach Hazards Statement",
                "sender": "w-nws.webmaster@noaa.gov",
                "senderName": "NWS Newport/Morehead City NC",
                "headline": "Beach Hazards Statement issued October 13 at 11:06AM EDT until October 13 at 8:00PM EDT by NWS Newport/Morehead City NC",
                "description": "* WHAT...Dangerous rip currents.\n\n* WHERE...The beaches north of Cape Hatteras.\n\n* WHEN...Until 8 PM EDT this evening.\n\n* IMPACTS...Rip currents can sweep even the best swimmers away\nfrom shore into deeper water.",
                "instruction": "Swim near a lifeguard. If caught in a rip current remain calm.\nDon''t fight the current. Swim in a direction following the\nshoreline. When out of the current, swim back to shore. If tired,\nfloat or tread water until out of the rip current. If unable to\nescape, face the shore and call or wave for help.",
                "response": "Avoid",
                "parameters": {
                    "NWSheadline": [
                        "BEACH HAZARDS STATEMENT REMAINS IN EFFECT UNTIL 8 PM EDT THIS EVENING"
                    ],
                    "VTEC": [
                        "/O.CON.KMHX.BH.S.0050.000000T0000Z-201014T0000Z/"
                    ],
                    "PIL": [
                        "MHXCFWMHX"
                    ],
                    "BLOCKCHANNEL": [
                        "CMAS",
                        "EAS",
                        "NWEM"
                    ],
                    "eventEndingTime": [
                        "2020-10-14T00:00:00+00:00"
                    ]
                }
            }
        }
    ],
    "title": "current watches, warnings, and advisories for North Carolina",
    "updated": "2020-10-13T21:03:31+00:00"
}')  ;


[stanl]

Yeah...I just didn't want you to think I was trying to push SQLite over what you were doing.  I really was curious.   Thought it might give me some ideas for new functions.

NP. we can end the thread here. I upped my Jscript learning curve and you illustrated how your extender could handle Json. You seem to be saying that SQLite, much like MySQL or MongoDB, can accomodate a Json column_type - probably a text field but with ability to detect valid Json, Iterate keys/properties/values and based on that query the column for additional output - which is EXCELLENT ;)


I do more in the world of Excel [sans 365].  Maybe someone who tests my script can confirm the Excel output can be obtained.

[JTaylor]

Yes.  I think the SQLite stuff was modeled after MySQL.   It seems to be very versatile and once you understand what it is doing there doesn't seem to be much you can't easily do with it in the context of JSON reading and manipulation.

Appreciate all your work here.

Jim

[td]

I do more in the world of Excel [sans 365].  Maybe someone who tests my script can confirm the Excel output can be obtained.

Did a quick, simple-minded test and it created an a non-360 Excel spread sheet.  As time permits will do more thorough testing, add some additional comments, and add the script to the Tech Database.

[stanl]

Did a quick, simple-minded test and it created an a non-360 Excel spread sheet.  As time permits will do more thorough testing, add some additional comments, and add the script to the Tech Database.

So, what kind of breweries where you live? :D

[td]

Well, this part of the world has more craft brewers per capita than just about any place on the planet.  Folks in these parts are a very happy lot.

[stanl]

I thought that would be Portland; aren't you closer to Seattle?. Anyway Nancy and I look for the best Amber. Used to brew my own at home and she made me a shirt for "Littlefield Amber"  "It's not just for breakfast"

[stanl]

One function in the script was not called. Seems I had fat-fingering issues and when testing it kept getting 'unsupported variant type'. Below works. It returns the Json string as an object and might make it easier to call the other functions using MsscriptControl.

Code (WINBATCH) Select Expand



#DefineFunction jsnObj(oS,jdata)
Return oS.Eval(: `(` : jdata : `)`)
#EndFunction


[td]

I thought that would be Portland; aren't you closer to Seattle?. Anyway Nancy and I look for the best Amber. Used to brew my own at home and she made me a shirt for "Littlefield Amber"  "It's not just for breakfast"

Seattle has new craft breweries opening up on almost a weekly basis even in the pandemic so it is a little difficult to say how many there actually are. I live in a forested rural area about 15 miles including a boat trip on a large body of water away from the Seattle city center.  Despite being rural there are at least 4 craft breweries within 5 miles of our home. There are several craft breweries on the Oregon side of the Columbia Gorge that in my somewhat biased opinion produce some of the best Belgian Strong Dark Ales to be found outside of Belgium. 

I am a home brewer as well. Homebrewing is very common in the Pacific Northwest. It could almost be described as an integral part of the regional culture.