Best way to handle large JSON data? MAP still supported?

[BJP]

Hello.  I have a program that has used ADO database connection to return a query and then subsequently put the query results into an array using getrows.  See snippet below:

$constrim="DRIVER={SQL Server};SERVER=xxx-xxxxx\CSC;DATABASE=xxxxxxxxx;uid=xxxxxxxxxx;pwd=xxxxxxxxxxxx;"
$adCN = ObjCreate ("ADODB.Connection") ; <== Create SQL connection
$adCN.Open ($constrim) ;
local $sQuery = "select * from tbl_Apps"                     ; get all applications in the database
local $oAppRecordSet = $adCN.Execute($sQuery)
local $aAppsInDB = $oAppRecordSet.Getrows(5000)

With the code above I can perform array operations very efficiently. 

Now I need to get this data via JSON which is working (using Ward's JSON UDF)  but the data set returned is large (approx 4MB) and I'm wondering what is the most efficient way to get this data into an array.  

Dim $obj = ObjCreate ("WinHttp.WinHttpRequest.5.1")
$obj.Open("GET", $URL, false)
$obj.SetRequestHeader("Content-Type", "application/json")
$obj.Send()
$json = JSON_decode( $obj.ResponseText )

Any help would be appreciated!!

[AspirinJunkie]

So what's your problem with Ward's UDF?
It seem to work for you but you search a faster solution - right?

Maybe my JSON-UDF is a good try for you.
This UDF convert a JSON-string into nested AutoIt-datastructures (strings, numbers, bools, arrays, dictionaries).

But it shouldn't be faster than Ward's because it's written in native AutoIt. Ward's based on a professional external JSON-parser.

Anyway - here's an example of how to use:

#include <JSON.au3>
Global $s_String = '[{"id":"4434156","url":"https://legacy.sky.com/v2/schedules/4434156","title":"468_CORE_1_R.4 Schedule","time_zone":"London","start_at":"2017/08/10 19:00:00 +0100","end_at":null,"notify_user":false,"delete_at_end":false,"executions":[],"recurring_days":[],"actions":[{"type":"run","offset":0}],"next_action_name":"run","next_action_time":"2017/08/10 14:00:00 -0400","user":{"id":"9604","url":"https://legacy.sky.com/v2/users/9604","login_name":"robin@ltree.com","first_name":"Robin","last_name":"John","email":"robin@ltree.com","role":"admin","deleted":false},"region":"EMEA","can_edit":true,"vm_ids":null,"configuration_id":"19019196","configuration_url":"https://legacy.sky.com/v2/configurations/19019196","configuration_name":"468_CORE_1_R.4"},{"id":"4444568","url":"https://legacy.sky.com/v2/schedules/4444568","title":"468_CORE_1_R.4 Schedule","time_zone":"London","start_at":"2017/08/11 12:00:00 +0100","end_at":null,"notify_user":false,"delete_at_end":false,"executions":[],"recurring_days":[],"actions":[{"type":"suspend","offset":0}],"next_action_name":"suspend","next_action_time":"2017/08/11 07:00:00 -0400","user":{"id":"9604","url":"https://legacy.sky.com/v2/users/9604","login_name":"robin@ltree.com","first_name":"Robin","last_name":"John","email":"robin@ltree.com","role":"admin","deleted":false},"region":"EMEA","can_edit":true,"vm_ids":null,"configuration_id":"19019196","configuration_url":"https://legacy.sky.com/v2/configurations/19019196","configuration_name":"468_CORE_1_R.4"}]'


; ================= parse JSON-string into AutoIt-datatypes (+Scripting.Dictionary) ==============
$o_Object = _JSON_Parse($s_String)


; ================= queries directly in AutoIt-syntax ================================
$s_Type = $o_Object[1].Item("actions")[0].Item("type")
ConsoleWrite("type: " & $s_Type & @CRLF)


; ================= queries with function JSON_Get (safer) ================================
$s_Type = _JSON_Get($o_Object, "[1].actions[0].type")
ConsoleWrite("type: " & $s_Type & @CRLF)


; ================= convert nestest AutoIt-datastructure into a JSON-string ==============
ConsoleWrite(_JSON_Generate($o_Object) & @CRLF & @CRLF)
; compact JSON:
ConsoleWrite(_JSON_Generate($o_Object, "", "", "", "", "", "") & @CRLF & @CRLF)

 

 

 

 

 

 

 

 

 

 

Edited March 2, 2023 by AspirinJunkie
Removed attached Json.au3 because there is now a thread for this

[junkew]

you should use the FOR JSON  clause in your SQL statement

https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server

[mLipok]

Maybe Chilkat free activex component and my UDF ?
 

 

 

Edited October 12, 2017 by mLipok