BJP

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

4 posts in this topic

#1 ·  Posted

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!!

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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)

 

 

 

 

 

 

JSON.au3

Edited by AspirinJunkie
wrong file attached / now faster on parsing arrays / even faster array processing

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Maybe Chilkat free activex component and my UDF ?
 

 

 

Edited by mLipok

Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now

  • Similar Content

    • baolo073
      By baolo073
      [ [ [ "Kinh Oanh,\r\n", "Dear Oanh,\r\n", null, null, 3 ], [ "C\u1ea3m \u01a1n b\u1ea1n r\u1ea5t nhi\u1ec1u v졢\u1ee9c th\u01b0 c\u1ee7a b\u1ea1n \u0111\u1ebfn m\u1ed9t v᩠ngṠtr\u01b0\u1edbc. ", "Thank you very much for your letter which arrived a few days ago.", null, null, 3 ], [ "Th\u1eadt \u0111⯧ y뵠khi nghe t\u1eeb b\u1ea1n.\r\n", "It was lovely to hear from you.\r\n", null, null, 3 ], [ "b\u1ea1n \u0111i \u0111㵠v\u1eady.\r\n", "where do you go.\r\n", null, null, 1 ], [ "T\u1ea1m bi\u1ec7t!", "Goodbye!", null, null, 1 ] ], null, "en", null, null, null, 0.91366601, null, [ [ "en" ], null, [ 0.91366601 ], [ "en" ] ] ] How to parse array to json?
    • NiftRex
      By NiftRex
      I'm trying to get an array from a website so that I can just get the url, but I am not sure how. I read a bit of arrays but I have a feeling I'd have to be writing a lot more than what I should be. I will include the script I have so far and the API url for what I want.
       
      API: https://api.fast.com/netflix/speedtest?https=true&token=YXNkZmFzZGxmbnNkYWZoYXNkZmhrYWxm&urlCount=1 (I want the 'url' array that contains the url)
       
      Code:
      #include <MsgBoxConstants.au3> #include <Inet.au3> #include <Array.au3> $site = _INetGetSource('http://api.fast.com/netflix/speedtest?https=true&token=YXNkZmFzZGxmbnNkYWZoYXNkZmhrYWxm&urlCount=1') MsgBox($MB_SYSTEMMODAL, "Title", $site[1])  
    • 31290
      By 31290
      Hi Guys, 
      Since I'm able to get a Dell equipment warranty status thanks to my API key, I'm using an UDF to extract data from an XML file and get the end date. > 
      Thing is, when using InetGet, the original file is in JSON format and the UDF is not working anymore, even if I download the file with the xml extension. Therefore, and when I manually download the page with Chrome, I have a proper XML file where the UDF is working fine.
      Here's my code:
      I even tried to convert the json to xml > https://www.autoitscript.com/forum/topic/185717-js-json-to-xml/
      I took a look here https://www.autoitscript.com/forum/topic/104150-json-udf-library-fully-rfc4627-compliant/ but I don't understand anything :/
       
      The XML read UDF is just perfect for my needs but I'm stuck here... 
      Thanks for any help you can provide
      -31290-
      3MTXM12.json
      3MTXM12.xml
    • dexto
      By dexto
      Why idea why would I get an error (Autoit Beta 3.3.15.0):
      Local $json[] Local $map[] $map['test'] = 'hello' Local $array[5] = [1, 2, 3, 4, 5] $json["data"] = $map $json["data"]["arr"] = $array ConsoleWrite($json["data"]["arr"][0] & @CRLF)  
    • cookiemonster
      By cookiemonster
      Im trying to read a JSON file into an array.
       
      The JSON looks like this:
       
      { "project_info": { "project_number": "123456789", "url": "https://www.website.com", "project_id": "PRJ08", "Bucket": "Buk09" }, "client": [ { "client_info": { "id": "1:1001306455", "info": { "name": "banana" } }, "oauth_client": [ { "client_id": "1001306455694-m3h6v", "client_type": 3 } ], "a_key": [ { "key": "dkldkdkd" } ], "Avail": { "ana": { "status": 1 }, "vit": { "status": 1, "other": [] }, "ad": { "status": 2 } } }, { "client_info": { "id": "1:1838346", "info": { "name": "orange" } }, "oauth_client": [ { "client_id": "2145696315633-dmdhe", "client_type": 3 } ], "a_key": [ { "key": "osikdme" } ], "Avail": { "ana": { "status": 1 }, "vit": { "status": 1, "other": [] }, "ad": { "status": 2 } } }, ], "configuration_version": "1" } What I want to do is read it all into one single large array, can anyone help? 
      All I have so far is:
      #include "JSON.au3" #include "array.au3" $file = fileread("C:\file.json") JsonToArray($file) Func JsonToArray($JSON) $JSON = StringRegExpReplace($JSON, "[\[\]{}]", "") $sBreak = StringSplit($JSON, ",") For $a = 1 To $sBreak[0] $t = _JSONDecode("{" & $sBreak[$a] & "}") _ArrayDisplay($t, "multi " & $a & " of " & $sBreak[0]) Next EndFunc ;==>JsonToArray