convert json data into an array

[TheXman]

 

On 1/25/2021 at 1:31 PM, gcue said:

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

If I were going to use jq to do what your example does, which is list all Overview.Name where ReviewableSystems.IsCollaborationRecorded = "Yes", then assuming the JSON below I would do something like:  (I added a couple of extra fields in the result, type & location, to show how easy it is to show additional fields in the process)

You could get a similar result using a SQLite query against the JSON data.

 

Assuming the following JSON file:

Spoiler

expandcollapse popup

[
  {
    "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": "Yes"
    }
  }
]

 

Example jq Script:

Spoiler

expandcollapse popup

#cs
    This example uses the jq UDF.
    https://www.autoitscript.com/forum/files/file/502-jq-udf-a-powerful-flexible-json-processor/
#ce

#include <Constants.au3>
#include <Array.au3>
#include <jq.au3> ;<== Modify as needed

;Initialize jq
_jqInit("jq-win64.exe") ;<== Modify as needed
If @error Then Exit MsgBox($MB_ICONERROR + $MB_TOPMOST, "ERROR", "ERROR: Unable to initialize jq - @error = " & @error)

;Execute the example
collab_names_example("Yes")

Func collab_names_example($sCollab)
    Local $sCmdOutput = ""
    Local $aArray[0][3]

    ;Find names where .ReviewableSystems.IsCollaborationRecorded equals $sCollab
    $sCmdOutput = _jqExecFile("test.json", _ ;<== Modify as needed
                              '.[] | ' & _
                              'select(.ReviewableSystems.IsCollaborationRecorded == "' & $sCollab & '") | ' & _
                              '[.Overview.Name, .Overview.Type, .Overview.Location] | ' & _
                              '@tsv' _
                              )

    ;Display result
    write_log_line(@CRLF & "======  Apps with collab  = " & $sCollab & "  =====")
    write_log_line($sCmdOutput & @CRLF)

    ;Convert result to an array and display it
    _ArrayAdd($aArray, $sCmdOutput, 0, @TAB, @CRLF)
    _ArrayDisplay($aArray, "Apps with collab  = " & $sCollab, "", 0, Default, "Name|Type|Location")
EndFunc

Func write_log_line($sMsg = "")
    ConsoleWrite($sMsg & @CRLF)
EndFunc

Which produces:

======  Apps with collab  = Yes  =====
AES Custom  ET

And a result array containing:

 

Edited January 27, 2021 by TheXman

[gcue]

wow that is really fast.  nice job TheXMan

thanks man

[TheXman]

You're welcome!  👍  Hopefully, the example will open your eyes to other ways to process JSON data.

Edited January 25, 2021 by TheXman