jq UDF - A Powerful & Flexible JSON Processor

[TheXman]

Thanks for pointing that out.  Since I don't have Windows 11 installed on any of my PC's, and doubt that I ever will, I'll have to come up with a different way to display those results. 

Edited November 13, 2022 by TheXman

[Danp2]

This should work for both scenarios --

Func write_log_line($sMsg = "")
    Const $TITLE_NOTEPAD = "[RegExpTitle:(?i)untitled - notepad]"
    Static $hWndNotepad = -1, $sEditClass = "Edit1"

    ;If we don't have a handle to notepad yet
    If $hWndNotepad = -1 Then
        ;If there isn't an existing instance of notepad running, launch one
        If Not WinExists($TITLE_NOTEPAD) Then Run("Notepad.exe")

        ;Get handle to notepad window
        $hWndNotepad = WinWait($TITLE_NOTEPAD, "", 3)
        If Not $hWndNotepad Then Exit MsgBox($MB_ICONERROR, "ERROR", "Unable to find Notepad window.")

        If ControlCommand($hWndNotepad, "", "[CLASS:RichEditD2DPT; INSTANCE:1]", "IsEnabled", "") Then
            $sEditClass = "[CLASS:RichEditD2DPT; INSTANCE:1]"
        EndIf
    EndIf

    ;Paste msg to notepad text
    If WinExists($hWndNotepad) Then
        ControlCommand($hWndNotepad, "", $sEditClass, "EditPaste", $sMsg & @CRLF)
    EndIf
EndFunc

 

[TheXman]

New version (v1.6.2) uploaded.  Click HERE to see version history.

[TheXman]

What's New in Version v1.7.0

Released 4 minutes ago

• Internal code optimizations
• Increased query speed
• jqExec() and _jqExecFile() now accept string filters with comments (#), as if they were passed in from a file using the -f or --from-file option.

 

jq UDF has a new companion tool for creating, testing, and learning JSON parsing and processing, it's called jqPlayground,  It can be found in the AutoIt Downloads area under "Information Gathering" or by clicking HERE.  It comes with over 15 JSON processing examples.  You can modify and play with the examples or create & test your own.

Edited February 23, 2023 by TheXman

[Bresacon]

Hi,
thanks a lot for this UDF, it works great but I've been having difficulties to work with extended ASCII characters inside JSON keys. Words like Português, Català, Español, Français are trouble.
 
Every time I try anything like ChrW() to send _jqExec the Unicode of the conflicting character I get a null error or this one:
"jq: error: syntax error, unexpected INVALID_CHARACTER, expecting $end (Windows cmd shell quoting issues?) at <top-level>, line 1".

As per the AutoIt3 documentation I've also tried this BinaryToString(StringToBinary("xxx", 4)), being xxx the non ASCII character, to no avail.

Can you help me? I don't have a clue how to solve this.

[TheXman]

I can't help you with your specific issue without seeing your script or an accurate example that reproduces the problem you're having.  I'm not sure why you are using ChrW() in relation to jqExec() or jqExecFile, it shouldn't be necessary.

The example script below shows the creation of a JSON dataset with extended ASCII and multibyte characters and also the listing of values from that newly created JSON dataset.  So as you can see, strings with extended ASCII and multibyte characters can be stored and retrieved without the need of ChrW() or any other transformation.

expandcollapse popup

#AutoIt3Wrapper_AU3Check_Parameters=-w 3 -w 4 -w 5 -w 6 -d

#include <Constants.au3>
#include <jq\jq.au3>

Const $JQ_EXE = "C:\Utils\jq\jq-win64.exe"

example()

Func example()
    Local $sOutput = "", _
          $sJson   = ""

    ;Init jq
    _jqInit($JQ_EXE)
    If @error Then return MsgBox($MB_ICONERROR, "_jqInit() Error", "@error = " & @error)

    ;Create a new JSON dataset with extended ASCII and multibyte chars
    $sJson = _jqExec("{}", _
                     '  setpath(["key1"]; "Português")' & _
                     '| setpath(["key2"]; "Català")'    & _
                     '| setpath(["key3"]; "Español")'   & _
                     '| setpath(["key4"]; "Français")'  & _
                     '| setpath(["key5"]; "こんにちは")' _
                     )
    If @error Then return MsgBox($MB_ICONERROR, "_jqExec() Error", "@error = " & @error & @CRLF & $sJson)

    ;Display result
    MsgBox($MB_ICONINFORMATION, "New JSON Dataset", $sJson)


    ;List all of the values from the JSON dataset that was created
    $sOutput = _jqExec($sJson, '.[]')
    If @error Then return MsgBox($MB_ICONERROR, "_jqExec() Error", "@error = " & @error & @CRLF & $sOutput)

    ;Display result
    MsgBox($MB_ICONINFORMATION, "List of JSON values", $sOutput)
EndFunc

 

Edited May 13, 2023 by TheXman

[Bresacon]

On 5/12/2023 at 7:44 PM, TheXman said:

I can't help you with your specific issue without seeing your script or an accurate example that reproduces the problem you're having.  I'm not sure why you are using ChrW() in relation to jqExec() or jqExecFile, it shouldn't be necessary.

The example script below shows the creation of a JSON dataset with extended ASCII and multibyte characters and also the listing of values from that newly created JSON dataset.  So as you can see, strings with extended ASCII and multibyte characters can be stored and retrieved without the need of ChrW() or any other transformation.

Thanks a lot for your quick response.
Ok. Let's use your example. As I mentioned, the problem I found was when the non ASCII characters are in the keys. So I'll change it.

Also, you're cheating. Because you're making a general request (sorry if I'm not using the right words), I need to call specifically the key with the extended ASCII character.

expandcollapse popup

#AutoIt3Wrapper_AU3Check_Parameters=-w 3 -w 4 -w 5 -w 6 -d

#include <Constants.au3>
#include <jq\jq.au3>

Const $JQ_EXE = "C:\Utils\jq\jq-win64.exe"

example()

Func example()
    Local $sOutput = "", _
          $sJson   = ""

    ;Init jq
    _jqInit($JQ_EXE)
    If @error Then return MsgBox($MB_ICONERROR, "_jqInit() Error", "@error = " & @error)

    ;Create a new JSON dataset with extended ASCII and multibyte chars
    $sJson = _jqExec("{}", _
                     '  setpath(["Português"]; "Português")' & _
                     '| setpath(["Català"]; "Català")'    & _
                     '| setpath(["Español"]; "Español")'   & _
                     '| setpath(["Français"]; "Français")'  & _
                     '| setpath(["こんにちは"]; "こんにちは")' _
                     )
    If @error Then return MsgBox($MB_ICONERROR, "_jqExec() Error", "@error = " & @error & @CRLF & $sJson)

    ;Display result
    MsgBox($MB_ICONINFORMATION, "New JSON Dataset", $sJson)


    ;List all of the values from the JSON dataset that was created
    $sOutput = _jqExec($sJson, '.[Português]')
    If @error Then return MsgBox($MB_ICONERROR, "_jqExec() Error", "@error = " & @error & @CRLF & $sOutput)

    ;Display result
    MsgBox($MB_ICONINFORMATION, "List of JSON values", $sOutput)
EndFunc

So, all I've changed is the asignation for both $sJson and $sOutput variables. If I run this I get this message:

 

And I don't know how to fix that.

Edited May 13, 2023 by Bresacon

[TheXman]

If you have a key with special characters, then it needs to be wrapped in quotes. 

expandcollapse popup

#AutoIt3Wrapper_AU3Check_Parameters=-w 3 -w 4 -w 5 -w 6 -d

#include <Constants.au3>
#include <jq\jq.au3>

Const $JQ_EXE = "C:\Utils\jq\jq-win64.exe"

example()

Func example()
    Local $sOutput = "", _
          $sJson   = ""

    ;Init jq
    _jqInit($JQ_EXE)
    If @error Then return MsgBox($MB_ICONERROR, "_jqInit() Error", "@error = " & @error)

    ;Create a new JSON dataset with extended ASCII and multibyte chars
    $sJson = _jqExec("{}", _
                     '  setpath(["Português"]; "Português")' & _
                     '| setpath(["Català"]; "Català")'    & _
                     '| setpath(["Español"]; "Español")'   & _
                     '| setpath(["Français"]; "Français")'  & _
                     '| setpath(["こんにちは"]; "こんにちは")' _
                     )
    If @error Then return MsgBox($MB_ICONERROR, "_jqExec() Error", "@error = " & @error & @CRLF & $sJson)

    ;Display result
    MsgBox($MB_ICONINFORMATION, "New JSON Dataset", $sJson)


    ;List value of key = Português and "こんにちは
    $sOutput = _jqExec($sJson, '.["Português"], ."こんにちは"') ; either format works (bracket-notation or dot-notation)
    If @error Then return MsgBox($MB_ICONERROR, "_jqExec() Error", "@error = " & @error & @CRLF & $sOutput)

    ;Display result
    MsgBox($MB_ICONINFORMATION, "List of JSON values", $sOutput)
EndFunc

Edited May 13, 2023 by TheXman

[Bresacon]

32 minutes ago, TheXman said:

If you have a key with special characters, then it needs to be wrapped in quotes. 

expandcollapse popup

#AutoIt3Wrapper_AU3Check_Parameters=-w 3 -w 4 -w 5 -w 6 -d

#include <Constants.au3>
#include <jq\jq.au3>

Const $JQ_EXE = "C:\Utils\jq\jq-win64.exe"

example()

Func example()
    Local $sOutput = "", _
          $sJson   = ""

    ;Init jq
    _jqInit($JQ_EXE)
    If @error Then return MsgBox($MB_ICONERROR, "_jqInit() Error", "@error = " & @error)

    ;Create a new JSON dataset with extended ASCII and multibyte chars
    $sJson = _jqExec("{}", _
                     '  setpath(["Português"]; "Português")' & _
                     '| setpath(["Català"]; "Català")'    & _
                     '| setpath(["Español"]; "Español")'   & _
                     '| setpath(["Français"]; "Français")'  & _
                     '| setpath(["こんにちは"]; "こんにちは")' _
                     )
    If @error Then return MsgBox($MB_ICONERROR, "_jqExec() Error", "@error = " & @error & @CRLF & $sJson)

    ;Display result
    MsgBox($MB_ICONINFORMATION, "New JSON Dataset", $sJson)


    ;List value of key = Português and "こんにちは
    $sOutput = _jqExec($sJson, '.["Português"], ."こんにちは"') ; either format works (bracket-notation or dot-notation)
    If @error Then return MsgBox($MB_ICONERROR, "_jqExec() Error", "@error = " & @error & @CRLF & $sOutput)

    ;Display result
    MsgBox($MB_ICONINFORMATION, "List of JSON values", $sOutput)
EndFunc

Of course. How silly of me.
Thanks and sorry for wasting your time. Hope it helps other dummies.

[TheXman]

It's not a waste of my time if you've learned something valuable.

You're welcome!  

[n3wbie]

Hello,

First of all thanks for wonderful udf. and also playground app.

i was just figuring out on the usage where i wish to under 1 concept Loop inside a loop situation.

Loop is well explained in create Seperated list of values (Multiple version)

But can we have multiple loops

I have a Json Document which i would like to share in person if possible

[TheXman]

n3wbie,

3 hours ago, n3wbie said:

But can we have multiple loops

You have not provided any details regarding what information you want to extract from your JSON dataset and in what particular format this information should be formatted.  Without more information, I cannot answer your specific question.  In general, using jq and AutoIt, you should be able process your JSON dataset and get just about any desired result (usually in a single jq call).

Why do you need to privately share your sample JSON dataset?  Unless there is a good reason, I would prefer to keep the whole dialog here so that others may be able to gain some knowledge from the exercise.

If you are trying to gain a better understanding of how you can process your JSON dataset, then I'd be willing to help.  To start, I'll need a better explanation of the problem, a detailed explanation of the desired output, and to see or know what you've tried so far (if anything).

Edited September 30 by TheXman

[n3wbie]

13 minutes ago, TheXman said:

n3wbie,

You have not provided any details regarding what information you want to extract from your JSON dataset and in what particular format this information should be formatted.  Without more information, I cannot answer your specific question.  In general, using jq and AutoIt, you should be able process your JSON dataset and get just about any desired result (usually in a single jq call).

Why do you need to privately share your sample JSON dataset?  Unless there is a good reason, I would prefer to keep the whole dialog here so that others may be able to gain some knowledge from the exercise.

If you are trying to gain a better understanding of how you can process your JSON dataset, then I'd be willing to help.  To start, I'll need a better explanation of the problem, a detailed explanation of the desired output, and to see or know what you've tried so far (if anything).

Request you to take the further discussion on Direct Message. Sample file that i have contains some sensitive information.

I can share both the files and what things i have already tried on Direct message to allow better understanding.

 

Update Json Structure

 

I need the data as

GSTIN | b2b.CTIN |b2b[].inv[].inum | b2b[].inv[].inum | b2b[].inv[].itms[].itm_det.txvalue

basically i need to have it in following format so that i can take the same in query

Edited September 30 by n3wbie
More Information being Provided

[TheXman]

n3wbie,

Even though we handled this through private messages, for others interested in seeing what jq filters of some complexity look like and can do, I am posting the final filter that provided your desired result set.

The jq filter:

.gstin as $gstin  |  # Store gstin
.fp    as $fp     |  # Store fp
.b2b[]            |  # For each b2b 
.ctin as $ctin    |  #   Store ctin
.inv[]            |  #   For each inv
[                    #     Output an array of values
  $gstin,
  $fp,
  $ctin,
  .inum,
  .idt,
  .inv_typ,
  .pos,
  .val,
  .itms[].itm_det.txval,
  .itms[].itm_det.rt,
  .itms[].itm_det.samt,
  .itms[].itm_det.camt
] |
join("|") # Join each array's elements using "|"

 

Edited October 2 by TheXman