JSON as AutoIt data type

[Skysnake]

I know how JSON works. However JSON data is typically received from web based servers. 

 

My question is:

Has anyone considered to use, or currently actively using JSON as an internal data container? 

Internal here as in an ordinary AutoIt data type, generated, populated and manipulated within an AutoIt script, without any external data sources. 

The JSON and BinaryCall UDFs work well and appear the de facto defaults. Are there any thoughts on using JSON for internal variable values instead of the typical array? 

Also, there can be situations where such a JSON collection contains an array and it will have to be parsed using typical array functions. 

Currently I am working on a small project where I have to keep and use various identifiers from different sources. There are several ideas in this regard, some involving SQL child tables and arrays stored with keys (maps), which lead me to think that JSON may be a more suitable data container. It readily shrinks and stretches, allowing for dynamic data growth.  JSONs ability to add new repeating data groups on the fly makes it particularly powerful and appealing. 

Ideas and suggestions welcome

Skysnake

Edited January 4, 2019 by Skysnake

[jchd]

FYI SQLite JSON1 functions work like a charm. I know you're using SQLite a lot so this may be a decent solution. Just store your JSON source in a temp table and peek-poke in from there. You can as well issue a direct query without having to store it first in case the desired operation (creation, extraction, modification) isn't repetitive.

[Skysnake]

It's funny how we miss the obvious. Yes, you are right about SQLite, and I often refer to the SQlite JSON documentation... For some reason the AutoIt-SQLite-JSON link has passed me by 

That's also an easy error checking device 

Will definitely have a look. Thanks for the reminder 

[argumentum]

5 hours ago, jchd said:

Just store your JSON source in a temp table and peek-poke in from there

...just saw this and google it ...and ... would you paste some simple code to give me a clue of how to go about this.  =) 

[TheXman]

19 minutes ago, argumentum said:

give me a clue of how to go about this

If you are looking for SQLite syntax examples, then you can start here:

https://www.sqlite.org/json1.html

[argumentum]

1 minute ago, TheXman said:

If you are looking for SQLite syntax examples

au3 example. I know those

[TheXman]

Here's a very simple example of validating that json is well-formed and extracting a value from well-formed JSON:

expandcollapse popup

#include <SQLite.au3>

example()

Func example()
        Const $TEST_JSON = _
                           '{' & _
                           '    "manifest_version": 2,' & _
                           '    "name": "Google Apps Certification app",' & _
                           '    "description": "Link to Google Apps Certification website",' & _
                           '    "version": "1.1",' & _
                           '    "icons": {' & _
                           '        "128": "128.png"' & _
                           '    },' & _
                           '    "app": {' & _
                           '        "urls": [' & _
                           '            "http://certification.googleapps.com/app-info/"' & _
                           '        ],' & _
                           '        "launch": {' & _
                           '            "web_url": "http://certification.googleapps.com/"' & _
                           '        }' & _
                           '    },' & _
                           '    "permissions": [' & _
                           '        "unlimitedStorage",' & _
                           '        "notifications"' & _
                           '    ]' & _
                           '}'

        Const $SQLITE_DLL = "c:\program files\sqlite\sqlite3.dll" ;<-- Change to the location of your sqlite dll

        Local $aRow
        Local $hQuery

        ;Init sqlite and create a memory db
        _SQLite_Startup($SQLITE_DLL, False, 1)
        If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
        _SQLite_Open()

        ;Example of validating json (Good return means JSON is well-formed)
        If _SQLite_Query(-1, "select json('" & $TEST_JSON & "');", $hQuery) = $SQLITE_OK Then
            While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
                ConsoleWrite("Minimized JSON = " & $aRow[0] & @CRLF)
            WEnd
        EndIf

        ;Example of extracting data
        If _SQLite_Query(-1, "select json_extract('" & $TEST_JSON & "', '$.version');", $hQuery) = $SQLITE_OK Then
            While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
                ConsoleWrite("version = " & $aRow[0] & @CRLF)
            WEnd
        EndIf

        ;Close db and shutdown sqlite
        _SQLite_Close()
        _SQLite_Shutdown()

EndFunc

 

[Skysnake]

expandcollapse popup

#cs ----------------------------------------------------------------------------

    AutoIt Version: 3.3.14.2
    Author:         TheXman
    JSON as AutoIt data type

    Script Function:
    https://www.autoitscript.com/forum/topic/197243-json-as-autoit-data-type/?do=findComment&comment=1414692

#ce ----------------------------------------------------------------------------

; Script Start - Add your code below here
#include <SQLite.au3>

example()

Func example()

    ; https://en.m.wikipedia.org/wiki/00_Agent
    Const $TEST_JSON = _
            '{' & _
            '    "James Bond": "007",' & _
            '    "Randy Enton": "006",' & _
            '    "Sam Johnston": "0012"' & _
            '}'
    ;;#CE

    Const $SQLITE_DLL = @ScriptDir & "\sqlite3.dll" ;<-- Change to the location of your sqlite dll

    Local $aRow
    Local $hQuery

    ;Init sqlite and create a memory db
    _SQLite_Startup($SQLITE_DLL, False, 1)
    If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
    _SQLite_Open()

    ;Example of validating json (Good return means JSON is well-formed)
    If _SQLite_Query(-1, "select json('" & $TEST_JSON & "');", $hQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            ConsoleWrite("Minimized JSON = " & $aRow[0] & @CRLF)
        WEnd
    EndIf

    ;Example of extracting data
    Local $sJSONValueToFind = "James Bond"
    ;;;$sJSONQuery = "select json_extract('" & $TEST_JSON & "', '$.James Bond') ;"
    $sJSONQuery = "select json_extract('" & $TEST_JSON & "', '$." & $sJSONValueToFind & "') ;"
    ConsoleWrite($sJSONQuery & @CRLF)
    If _SQLite_Query(-1, $sJSONQuery, $hQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            ConsoleWrite($sJSONValueToFind & ": " & $aRow[0] & @CRLF)
        WEnd
    EndIf


    ;Example of setting data
    Local $sJSONAttribToSet = "Stuart Thomas"
    Local $sJSONValueToSet = "005"
    Local $sJSONValueToFind = "Stuart Thomas"
    ;;;$sJSONQuery = "select json_extract('" & $TEST_JSON & "', '$.e4') ;"
    $sJSONQuery = "select json_set('" & $TEST_JSON & "', '$." & $sJSONAttribToSet & "'," & $sJSONValueToSet & ") ;"
    ConsoleWrite("88 " & $sJSONQuery & @CRLF)
    If _SQLite_Query(-1, $sJSONQuery, $sJSONQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($sJSONQuery, $aRow) = $SQLITE_OK
            ConsoleWrite("91 " & $sJSONValueToFind & ": " & $aRow[0] & @CRLF)
            Local $improvedJSON = $aRow[0]
        WEnd
    EndIf


    ;Local $improvedJSON = $aRow[0]
    ConsoleWrite("$improvedJSON " & $improvedJSON & @CRLF)

    $sJSONQuery = "select json_extract('" & $improvedJSON & "', '$." & $sJSONValueToFind & "') ;"
    ConsoleWrite("98 " & $sJSONQuery & @CRLF)
    If _SQLite_Query(-1, $sJSONQuery, $hQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            ConsoleWrite("101 " & $sJSONValueToFind & ": " & $aRow[0] & @CRLF)
        WEnd
    EndIf



    ;Close db and shutdown sqlite
    _SQLite_Close()
    _SQLite_Shutdown()

EndFunc   ;==>example

@TheXman I modded your code

Added a SQLite JSON_SET statement.  https://www.sqlite.org/json1.html#jins see 4.5 

I am struggling with

• How to insert the JSON string into the SQLite table (probably just a syntax issue)
• and how to save '007' as string and not a number ie '007' and not '7'

Thx

Skysnake

Edited January 22, 2019 by Skysnake
typo

[jchd]

2 minutes ago, Skysnake said:

How to insert the JSON string into the SQLite table (probably just a syntax issue)

JSON is text so you can simply and safely store it in a TEXT column.

31 minutes ago, Skysnake said:

how to save '007' as string and not a number ie '007' and not '7'

You need to quote the value, like this:

expandcollapse popup

; Script Start - Add your code below here
#include <SQLite.au3>

example()

Func example()
    Const $TEST_JSON = _
            '{' & _
            '    "James Bond": "007",' & _
            '    "Randy Enton": "006",' & _
            '    "Sam Johnston": "0012"' & _
            '}'

    Const $SQLITE_DLL = "C:\SQLite\bin\sqlite3.dll" ;<-- Change to the location of your sqlite dll
    Local $aRow
    Local $hQuery

    ;Init sqlite and create a memory db
    _SQLite_Startup($SQLITE_DLL, False, 1)
    If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
    _SQLite_Open()

    ;Example of validating json (Good return means JSON is well-formed)
    If _SQLite_Query(-1, "select json('" & $TEST_JSON & "');", $hQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            ConsoleWrite("Source JSON = " & $aRow[0] & @CRLF)
        WEnd
    EndIf

    ;Example of setting data
    Local $sJSONAttribToSet = "Stuart Thomas"
    Local $sJSONValueToSet = "005"
    Local $sJSONValueToFind = $sJSONAttribToSet
    ; #### note double quotes around $sJSONValueToSet ####
    $sJSONQuery = "select json_set('" & $TEST_JSON & "', '$." & $sJSONAttribToSet & "',""" & $sJSONValueToSet & """) ;"
    Local $sChangedJSON
    If _SQLite_Query(-1, $sJSONQuery, $sJSONQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($sJSONQuery, $aRow) = $SQLITE_OK
            $sChangedJSON = $aRow[0]
        WEnd
    EndIf

    ConsoleWrite("$sChangedJSON " & $sChangedJSON & @CRLF)

    $sJSONQuery = "select json_extract('" & $sChangedJSON & "', '$." & $sJSONValueToFind & "') ;"
    ConsoleWrite($sJSONQuery & @CRLF)
    If _SQLite_Query(-1, $sJSONQuery, $hQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            ConsoleWrite($sJSONValueToFind & ": " & $aRow[0] & @CRLF)
        WEnd
    EndIf



    ;Close db and shutdown sqlite
    _SQLite_Close()
    _SQLite_Shutdown()

EndFunc   ;==>example

 

[TheXman]

Hi SkySnake -

Below you will see a slighly modified version of your script.  I identified my modifications by wrapping them in

"; =========== Modified  ====================="

Also notice that I changed the method of validating json by changing the function to json_valid().

expandcollapse popup

#cs ----------------------------------------------------------------------------

    AutoIt Version: 3.3.14.2
    Author:         TheXman
    JSON as AutoIt data type

    Script Function:
    https://www.autoitscript.com/forum/topic/197243-json-as-autoit-data-type/?do=findComment&comment=1414692

#ce ----------------------------------------------------------------------------

; Script Start - Add your code below here
#include <SQLite.au3>

example()

Func example()

    ; https://en.m.wikipedia.org/wiki/00_Agent
    Const $TEST_JSON = _
            '{' & _
            '    "James Bond": "007",' & _
            '    "Randy Enton": "006",' & _
            '    "Sam Johnston": "0012"' & _
            '}'
    ;;#CE

    Const $SQLITE_DLL = "c:\program files\sqlite\sqlite3.dll" ;<-- Change to the location of your sqlite dll

    Local $aRow
    Local $hQuery

    ;Init sqlite and create a memory db
    _SQLite_Startup($SQLITE_DLL, False, 1)
    If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
    _SQLite_Open()

; =========== Modified  =====================
    Local $sQuery, $sKey
    Local $vValue

    ;Example of validating json (Good return means JSON is well-formed)
    $sQuery = StringFormat("SELECT json_valid('%s')", $TEST_JSON)
    ConsoleWrite(@CRLF & "Query: " & $sQuery & @CRLF)
    If _SQLite_Query(-1, $sQuery, $hQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            ConsoleWrite("Value: " & $aRow[0] & @CRLF)
        WEnd
    EndIf

    ;Example of validating json (Good return means JSON is well-formed)
    $sKey   = "Stuart Thomas"
    $vValue = "007"
    $sQuery = StringFormat("SELECT json_set('%s', '$.%s','%s')", $TEST_JSON, $sKey, $vValue)
    ConsoleWrite(@CRLF & "Query: " & $sQuery & @CRLF)
    If _SQLite_Query(-1, $sQuery, $hQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            ConsoleWrite("Value: " & $aRow[0] & @CRLF)
        WEnd
    EndIf

    ConsoleWrite(@CRLF)
; =========== Modified  =====================

    ;Example of extracting data
    Local $sJSONValueToFind = "e4"
    ;;;$sJSONQuery = "select json_extract('" & $TEST_JSON & "', '$.e4') ;"
    $sJSONQuery = "select json_extract('" & $TEST_JSON & "', '$." & $sJSONValueToFind & "') ;"
    ConsoleWrite($sJSONQuery & @CRLF)
    If _SQLite_Query(-1, $sJSONQuery, $hQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            ConsoleWrite($sJSONValueToFind & ": " & $aRow[0] & @CRLF)
        WEnd
    EndIf


    ;Example of setting data
    Local $sJSONAttribToSet = "Stuart Thomas"
    Local $sJSONValueToSet = "005"
    Local $sJSONValueToFind = "Stuart Thomas"
    ;;;$sJSONQuery = "select json_extract('" & $TEST_JSON & "', '$.e4') ;"
    $sJSONQuery = "select json_set('" & $TEST_JSON & "', '$." & $sJSONAttribToSet & "'," & $sJSONValueToSet & ") ;"
    ConsoleWrite("88 " & $sJSONQuery & @CRLF)
    If _SQLite_Query(-1, $sJSONQuery, $sJSONQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($sJSONQuery, $aRow) = $SQLITE_OK
            ConsoleWrite("91 " & $sJSONValueToFind & ": " & $aRow[0] & @CRLF)
            Local $improvedJSON = $aRow[0]
        WEnd
    EndIf


    ;Local $improvedJSON = $aRow[0]
    ConsoleWrite("$improvedJSON " & $improvedJSON & @CRLF)

    $sJSONQuery = "select json_extract('" & $improvedJSON & "', '$." & $sJSONValueToFind & "') ;"
    ConsoleWrite("98 " & $sJSONQuery & @CRLF)
    If _SQLite_Query(-1, $sJSONQuery, $hQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            ConsoleWrite("101 " & $sJSONValueToFind & ": " & $aRow[0] & @CRLF)
        WEnd
    EndIf



    ;Close db and shutdown sqlite
    _SQLite_Close()
    _SQLite_Shutdown()

EndFunc   ;==>example

 

 

By the way, although sqlite has some nice json functionality, you may want to check out my new UDF that lets you run jq. You can read more about it here:

 

Edited January 22, 2019 by TheXman
Fixed the comments to accurately represent the code blocks

[argumentum]

2 hours ago, TheXman said:

you may want to check out my new UDF that lets you run jq

I've tried it and find it slow. Good but 0.5 sec. to do an operation.    =/

[TheXman]

1 hour ago, argumentum said:

I've tried it and find it slow.

Yes, since jq itself is a command line tool, it is a bit slower than say a dll, especially if one is just retrieving a lot of scalar values.  However, it is very powerful.  I can definitely imagine more advanced scenarios in which jq may not only be faster, but much easier too.   If json slicing and dicing is necessary, or if you need to do mathematical functions against json data like averages or summing, or even need to extract, sort,  and/or generate json from existing json files, jq may be a better choice and a lot easier to use.  In addition, there are many things that jq can do that other json parsers cannot.  That's primarily because jq is a json processing tool as opposed to a json parser.  In any case, It's just another tool for the tool box. 

...And thanks for trying it out. 

Edited January 22, 2019 by TheXman

[Skysnake]

14 hours ago, jchd said:

You need to quote the value, like this:

  @jchd  you posted elsewhere a very handy little function for quoting standard SQL strings: _SQLStr() which quotes and doubles ' inline.

What would the JSON equivalent be? _SQLJSON() ...

Func _SQLJSON($s)
    Return('""' & StringReplace($s, "'", "''") & '""')
EndFunc

Replace ' with double for SQL , and use double "" around the JSON value...

Am I on the right track?

@TheXman & @argumentum thx.

 

[jchd]

5 hours ago, Skysnake said:

Am I on the right track?

There's an extra " around the result, but that isn't the whole story:

expandcollapse popup

#include <SQLite.au3>

Const $SQLITE_DLL = "C:\SQLite\bin\sqlite3.dll" ;<-- Change to the location of your sqlite dll
Local $aRow

;Init sqlite and create a memory db
_SQLite_Startup($SQLITE_DLL, False, 1)
If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
_SQLite_Open()

Local $sTEST_JSON = "Mc'Donald's" & @TAB & 'food / if you call that "food" \' & @CRLF & "this is the last verse."
Local $sEscapedJSON = _JSON_SQL_Escape($sTEST_JSON)
ConsoleWrite("Escaped JSON = " & $sEscapedJSON & @LF)

If _SQLite_QuerySingleRow(-1, "select json_extract(" & $sEscapedJSON & ", '$');", $aRow) = $SQLITE_OK Then
    ConsoleWrite("Valid JSON = " & $aRow[0] & @LF)
EndIf

Exit

; Escape chars in JSON strings as per rfc-7159 (http://www.rfc-editor.org/rfc/rfc7159.txt)
; then returns a valid SQL string containing a valid JSON string type.
;
; Literal single & double quotes are escaped to hex to avoid issues
; this is less human-readable but rock-solid (see below for full Unicode)
;
; If you expect Unicode chars outside the BMP (Basic Multilingual Plane)
; you need to convert codepoints > 0xFFFF to their surrogate pair in Hex

Func _JSON_SQL_Escape($s)
    ; escape / \ with a backslash
    $s = StringRegExpReplace($s, '([\\/])', '\\$1')
    ; escape single quotes to hex
    $s = StringRegExpReplace($s, "'", '\\u0027')
    ; escape solidus, backslash, double quotes and control chars to Hex
    ; then enclose the whole string in double quotes so that it's then a valid JSON literal string
    ; then enclose it in single quotes so that the SQL parser sees a valid SQL string
    Return '''"' & Execute('"' & StringRegExpReplace($s, '(["\x00-\x1f])', '" & "\\u" & Hex(Asc(''$1''), 4) & "') & '"') & '"'''
EndFunc

 

Edited January 23, 2019 by jchd

[Skysnake]

@jchd thank you kindly