convert json data into an array

[gcue]

hello all

i have a huge json file 1.5 MB and each entry is in in this format (includes only 2 entries):

Quote

[{"Overview":{"Classification":"Application","Status":"Standard","GId":"A0001267","Name":"REDP (North America)","AlternateName":"REDP (North America)","Description":"US Web Site - Home of ESS, EW2, and Force Administration\n\nOld Force DATM - A0002081 - Force","ManufacturerOrVendor":"REDP","Type":"SaaS","Location":"Cloud","ReviewCycle":"Semi-annually","CreatedBy":"DATM - 04/19/2013 12:06 PM","LastModifiedBy":"GERA - 11/04/2020 08:40 AM"},"ReviewableSystems":{"InternetFacing":"No","GeneratesAutomatedEmail":"No","Is13GApp":"No","IsSOC1App":"No","SupportsCBTnPCSServices":"No","IsCollaborationRecorded":"No"}},{"Overview":{"Classification":"Application","Status":"Divesting","GId":"A0001273","Name":"AES","AlternateName":"Attribution Extract System","Description":"Performance Attribution is a set of techniques that folks use to explain the discrepancy between performance against the benchmark set for it.  i.e. It is the return of a calculated view.  EES provides the ability for the Results users to determine what will be sent to Attribution.","ManufacturerOrVendor":"ET Internal","Type":"Custom","Location":"ET","ReviewCycle":"Semi-annually","CreatedBy":"DATM - 05/22/2013 10:37 AM","LastModifiedBy":"GMG - 12/14/2020 12:31 PM"},"ReviewableSystems":{"InternetFacing":"No","GeneratesAutomatedEmail":"No","Is13GApp":"No","IsSOC1App":"No","SupportsCBTnPCSServices":"No","IsCollaborationRecorded":"No"}}]

i've tried multiple json udfs but none of them work to make an array

thanks in advance!

[TheXman]

Saying that you want to convert JSON to an array means almost nothing.  It doesn't speak to what values you're interested in, how the array should be structured in terms of dimensions, and so on.  Depending on what you ultimately are trying to achieve, and the tool that you use to process the JSON data, you may not even need an array.  Instead of saying that you want to create an array, how about discussing what type of information you are trying to gather from the data. 

Also, if you have tried multiple JSON udfs, why don't you show what you've tried and where you've come up short?  That might help others to figure out what you are ultimately trying to do (at least in terms of the array you're trying to create).

[Nine]

For the fun of it :

#include <Constants.au3>
#include <Array.au3>

Local $aArray = StringRegExp(FileRead("Test.json"),'"([^"]*)":"([^"]*)"', 3)
Local $aFinal = _Array_Resize($aArray, 2)
_ArrayDisplay($aFinal)

Func _Array_Resize(ByRef $aArray1d, $iColumns, $iStart = Default)
  If IsKeyword($iStart) Then $iStart = 0
  Local $iElements = UBound($aArray1d) - $iStart
  If Mod($iElements, $iColumns) <> 0 Then Return SetError(1, 0, False)
  Local $aArray2d[$iElements / $iColumns][$iColumns]
  For $i = 0 To $iElements - 1
    $aArray2d[Floor($i / $iColumns)][Mod($i, $iColumns)] = $aArray1d[$i + $iStart]
  Next
  Return $aArray2d
EndFunc   ;==>_Array_Resize

 

[gcue]

6 hours ago, Nine said:

For the fun of it :

#include <Constants.au3>
#include <Array.au3>

Local $aArray = StringRegExp(FileRead("Test.json"),'"([^"]*)":"([^"]*)"', 3)
Local $aFinal = _Array_Resize($aArray, 2)
_ArrayDisplay($aFinal)

Func _Array_Resize(ByRef $aArray1d, $iColumns, $iStart = Default)
  If IsKeyword($iStart) Then $iStart = 0
  Local $iElements = UBound($aArray1d) - $iStart
  If Mod($iElements, $iColumns) <> 0 Then Return SetError(1, 0, False)
  Local $aArray2d[$iElements / $iColumns][$iColumns]
  For $i = 0 To $iElements - 1
    $aArray2d[Floor($i / $iColumns)][Mod($i, $iColumns)] = $aArray1d[$i + $iStart]
  Next
  Return $aArray2d
EndFunc   ;==>_Array_Resize

 

that works great nine -thanks!.. however the col 0 should be the headings and col 1 are all the row values

Edited January 24, 2021 by gcue

[gcue]

6 hours ago, TheXman said:

Saying that you want to convert JSON to an array means almost nothing.  It doesn't speak to what values you're interested in, how the array should be structured in terms of dimensions, and so on.  Depending on what you ultimately are trying to achieve, and the tool that you use to process the JSON data, you may not even need an array.  Instead of saying that you want to create an array, how about discussing what type of information you are trying to gather from the data. 

Also, if you have tried multiple JSON udfs, why don't you show what you've tried and where you've come up short?  That might help others to figure out what you are ultimately trying to do (at least in terms of the array you're trying to create).

i thought array since the json is so huge, thought an array would be the easiest to search through (in case there are multiple seperate searches i wanted to perform against it)

[junkew]

JSON looks simple just like regex but it really depends on your goal

read http://seriot.ch/parsing_json.php

maybe someone can make this running 😉

$pcre_regex=""
;$pcre_regex ='(*UCP)'

$pcre_regex +='/(?(DEFINE)'
$pcre_regex +='(?<number>  -?(?=[1-9]|0(?!\d))\d+(\.\d+)?([eE][+-]?\d+)?)'
$pcre_regex +='(?<boolean> true | false | null )'
$pcre_regex +='(?<string>  "(?>[^"\\\\]* | \\\\ ["\\\\bfnrt\/] | \\\\ u [0-9a-f]{4} )* " )'
$pcre_regex +='(?<array>   \[ (?> \g<json> (?: , \g<json> )* )? \s* \] )'
$pcre_regex +='(?<pair>    \s* \g<string> \s* : \g<json> )'
$pcre_regex +='(?<object>  \{ (?> \g<pair> (?: , \g<pair> )* )? \s* \} )'
$pcre_regex +='(?<json>    \s* (?> \g<number> | \g<boolean> | \g<string> | \g<array> | \g<object> ) \s* )'
$pcre_regex +=')'
$pcre_regex +='\A \g<json> \Z'
$pcre_regex +='/uix'

;$pcre_regex='"([^"]*)":"([^"]*)"'

Local $aArray = StringRegExp(FileRead("c:\temp\testjson.json"),$pcre_regex, 3)

 

[TheXman]

@gcue:

You only answered the least important question that I asked.  You didn't provide any information regarding what information you're trying to gather from the JSON data, what you've tried, which UDFs you attempted to use, and where or how your attempts came up short.

JSON is simple when you use the right tools for the job.  Using regular expressions to just parse JSON data is very fast, but it can be quite complex to cover all of the necessary criteria when compared to purpose-built tools that are designed to work specifically with JSON,  Nevertheless, for parsing JSON, it can be made to work most of the time.  It's sort of like using a rock to drive nails into wood.  It'll get the job done but there are much better tools for the job.  Slight changes in the string representation of JSON data (like the order or formatting of sets of "name: value" pairs) can cause a regular expression to fail and have to be modified, whereas a JSON tool wouldn't be affected by such changes because it is looking at it as JSON not strings.  When/If you need to process JSON data, that's where purpose-built JSON tools really shine.  The difference between parsing JSON and processing JSON, is the difference between gathering data from your JSON and gathering information.  I guess whether one chooses to use a rock (regexes, arrays, script code) or a hammer (purpose-built JSON tools) to drive nails (process JSON) really depends on one's current skill level, desire/ability to grasp new skills and how often one needs to drive nails (works with JSON). 

As you can see if you read the topic below, the sample JSON file provided was around 27 MB and had a much more complex structure than yours.  The goal of that person was to create a CSV records, of a small subset of the JSON data -- which isn't much different than generating an array of the data.  So basically it was a parsing exercise, not a processing exercise.  The generated CSV file data was over 52,000 lines.  There were several solutions offered and most of them took under 2 seconds to generate.  If you still haven't found your "perfect" solution yet, maybe you can get some more ideas from that topic.

 

Edited January 25, 2021 by TheXman
Corrected size of sample JSON file (1.9 MB -> 27 MB)

[gcue]

i deleted other attempts but this had been the most promising (but still couldnt figure out what i could not see values keep getting error 1 when processing json_get)
 

#include <json.au3>

$data = FileRead($json_complete_file)
$json_data = json_decode($data)

If @error Then MsgBox(0, "", @error)

Local $value
For $x = 0 To 20
    ;MsgBox(0,"","test")
    $value = json_get($json_data, "[" & $x & "][ID]")
    If @error Then
        msgbox(0,"", @error)
        ExitLoop
    EndIf

    msgbox(0,"","ID: " & $value)
Next

which i used from this udf

those speeds you mention sound great.  sounds like processing is better than parsing for sure.  i read through the link you sent and a common method uses stringregexp which is a little over my head so ward's udf sounds like it would be the easiest for me.

Edited January 25, 2021 by gcue

[TheXman]

55 minutes ago, gcue said:

sounds like processing is better than parsing for sure

One is not better than the other.  Each has its place.  Sometimes one just wants to pull data from a larger data set (parsing).  But in other cases, one really wants to get some sort of information from the data set, like getting counts, or finding specific values based on simple or complex criteria, or summing/averaging the values of specific items (processing).  Of course processing could be done by parsing and then using AutoIt commands and functions to process that parsed data.  However, I have found that it is easier to process JSON data using purpose-built JSON tools, both in terms of using the tool's language to get the information and the ease of maintenance.  If you work with JSON a lot, then JSON-based tools are great tools to have and use.  If you don't work with JSON a lot or have a hard time or no desire to learn how to use new tools, then the parse and process method is probably better than the pure JSON-based processing method.

Edited January 25, 2021 by TheXman

[gcue]

thanks for explaining when each is better.  any idea why i get error 1 in the script above?  im open to using a different method just need to perform multiple queries to the same data

[Nine]

From the example you gave in OP, what is the expected result, it would be easier for us to give a rightful solution...

[gcue]

your stringregexp worked!  the col 0 produced should be the headings values and col 1 produced should be all the row values

[Nine]

Please give an example of the result, I am unsure what you mean...

[gcue]

[TheXman]

3 hours ago, gcue said:

i deleted other attempts but this had been the most promising (but still couldnt figure out what i could not see values keep getting error 1 when processing json_get)
 

#include <json.au3>

$data = FileRead($json_complete_file)
$json_data = json_decode($data)

If @error Then MsgBox(0, "", @error)

Local $value
For $x = 0 To 20
    ;MsgBox(0,"","test")
    $value = json_get($json_data, "[" & $x & "][ID]")
    If @error Then
        msgbox(0,"", @error)
        ExitLoop
    EndIf

    msgbox(0,"","ID: " & $value)
Next

 

 

3 hours ago, gcue said:

any idea why i get error 1 in the script above?

I don't know what type of game you're trying to play, but I'm not going to be a part of it!  Either you are dealing with a completely different JSON file than the one you posted (in which you are trying to hide something) or you just put together some crap code to make it look like you actually attempted something.  Either way, there is no "ID" field in the JSON you provided.  So why would you expect that script to work?

I have asked you twice, what information are you trying to get from your JSON data.  You have avoided answering my question both times.  I can't answer your questions without answers to mine.  So I am done!  Someone else can try to get through to you. 

Edited January 25, 2021 by TheXman

[gcue]

sorry for the confusion.  no games - just trying to get this to work.  the id label was from an example i saw.  i had tried putting in other labels from my data and they didnt work so i thought ID was a generic value label and it was the last thing i tried.. 

i have a huge list of application names and i am trying to identify other corresponding fields IF the application matches.. so i have to cycle through all the application NAME fields to see which one matches then if it does - show fields that correspond to that application name

hope that makes sense.  sorry for the confusion

[gcue]

eureka!  thanks for the clue on ID not being a generic ID... i tried this and got values!

$sApp = Json_Get($oJson, "[" & $i & "][Overview.Name]")

    If @error Then ExitLoop

    $sCollab = Json_Get($oJson, "[" & $i & "][ReviewableSystems.IsCollaborationRecorded]")

    if StringInStr($sCollab, "Yes") <> 0 Then
        ConsoleWrite("App: " & $sApp & @CRLF)
        ConsoleWrite("Collab: " & $sCollab & @CRLF & @CRLF)
    EndIf

sorry again for the mix-up

[junkew]

Open it in notepad++ and find all in current document you directly have your records for value "REDP (North America)"

({"Classification)(.*?)("Name":)("REDP \(North America\)")(.*?})

 

[junkew]

It looks still you just want a straightforward table which you could do with a regex as was given before

If you are just interested in your 2 fields name and IsCollaborationRecorded you could do it like this (under the assumption your fields are allways there)

#include <Constants.au3>
#include <Array.au3>
$JSONString=FileRead("Test.json")

Local $aArray = StringRegExp($JSONString,'{"Classification.*?"Name":"(.*?)",.*?"IsCollaborationRecorded":"(.*?)"}',4)
for $i=0 to ubound($aArray)-1
   $arr2=$aArray[$i]
   ;consolewrite($Arr2[1] & @TAB & $Arr2[2] & @TAB & $Arr2[0] & @CRLF)
   consolewrite($Arr2[1] & @TAB & $Arr2[2] & @TAB & $Arr2[0] & @CRLF)
next

 

[gcue]

very cool

thanks Nine,TheXman,Junkew!