SQLite returning query as JSON

[argumentum]

expandcollapse popup

#include <SQLite.au3>

;-- When SQLite is compiled with the JSON1 extensions it provides builtin tools
;-- for manipulating JSON data stored in the database.
;-- This is a gist showing SQLite return query as a JSON object.
;-- https://www.sqlite.org/json1.html

Example()
Func Example()
    _SQLite_Startup() ; "<your path>\sqlite3.dll", False, 1) ; https://www.autoitscript.com/autoit3/docs/libfunctions/_SQLite_Startup.htm
    _SQLite_Open() ;  ...if you can not run this due to errors, get the latest DLL from https://www.sqlite.org/
    If _SQLite_Exec(-1, "CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, full_name TEXT NOT NULL, email TEXT NOT NULL, created DATE NOT NULL );") Then Return 4
    If _SQLite_Exec(-1, 'INSERT INTO users VALUES ' & _
            '(1, "Bob McFett", "bmcfett@hunters.com", "32-01-01"),' & _
            '(2, "Angus O''Vader","angus.o@destroyers.com", "02-03-04"),' & _
            '(3, "Imperator Colin", "c@c.c", "01-01-01");') Then Return 5

;   -- Get query data as a JSON object using the
;   -- json_group_object() [1] and json_object() [2] functions.
    _SQLite_GetTable2d_ArrayToConsole("SELECT" & _
            " json_group_object(" & _
            "   email," & _
            "   json_object('full_name', full_name, 'created', created)" & _
            " ) AS json_result" & _
            " FROM (SELECT * FROM users WHERE created > ""02-01-01"");")
    ; {"bmcfett@hunters.com":{"full_name":"Bob McFett","created":"32-01-01"},"angus.o@destroyers.com":{"full_name":"Angus O'Vader","created":"02-03-04"}}


;   -- Get query data as a JSON object using the
;   -- json_group_array() function to maintain order.
    _SQLite_GetTable2d_ArrayToConsole("SELECT" & _
            " json_group_array(" & _
            "   json_object('full_name', full_name, 'created', created)" & _
            " ) AS my_json_result_OrAnythingReally" & _
            " FROM (SELECT * FROM users ORDER BY created);")
    ; [{"full_name":"Imperator Colin","created":"01-01-01"},{"full_name":"Angus O'Vader","created":"02-03-04"},{"full_name":"Bob McFett","created":"32-01-01"}]

;-- Links
;-- [1] https://www.sqlite.org/json1.html#jgroupobject
;-- [2] https://www.sqlite.org/json1.html#jobj
; example found at https://gist.github.com/akehrer/481a38477dd0518ec0086ac66e38e0e2
EndFunc   ;==>Example

Func _SQLite_GetTable2d_ArrayToConsole($sSQL, $hDB = -1)
    Local $aResult, $iRows, $iColumns
    If _SQLite_GetTable2d($hDB, $sSQL, $aResult, $iRows, $iColumns) Then
        ConsoleWrite("! SQLite Error: " & _SQLite_ErrCode($hDB) & @CRLF & "! " & _SQLite_ErrMsg($hDB) & @CRLF)
    Else
        _SQLite_Display2DResult($aResult)
    EndIf
    ConsoleWrite(@CRLF)
EndFunc   ;==>_SQLite_GetTable2d_ArrayToConsole

Based on this example, you can build your own query. 
The code has all the explanations.
Enjoy  

[dmob]

Thank you for the examples; I battled to wrap my head around JSON group objects and ended up doing it the looong way

[jchd]

Nice, but as I said in another thread, you have to build your own formatting depending on each query, just to put column names in the resulting json.

An ideal solution would be a function like this:

select jsonify(select ...) whatever the inner select is.

[argumentum]

...same here @dmob, but I've got lucky.  
 

1 hour ago, jchd said:

An ideal solution would be a function like this: select jsonify(select ...) whatever the inner select is.

yes. And that is what I did  something along the idea is what I did for my testing tho, is not a general purpose func. , just a hack to get it out of my way:

Func JsonIze_Query($s, $sQuery)
    Local $s_json_object_Str = "", $sSQL = "SELECT json_group_array( json_object("
    Local $a_json_object_Str = StringSplit($s, ",")
    For $n = 1 To $a_json_object_Str[0]
        $s_json_object_Str &= ",'" & $a_json_object_Str[$n] & "'," & $a_json_object_Str[$n]
    Next
    $sSQL &= StringTrimLeft($s_json_object_Str, 1) & ")) AS json_result FROM (" & $sQuery & ");"
    ConsoleWrite($sSQL & @CRLF)
    Return $sSQL
EndFunc

 

Edited March 8, 2020 by argumentum
clarify a bit

[jchd]

I'm not trying to belittle your good code, not at all. What I find unpractical but inevitable is that you still have to setup both $s and $sQuery in paired terms for every distinct query. Either this way, or merge both when typing the query as in the other thread.

Deriving $s from the analysis of $sQuery automagically is way beyond doable for the general case (SQL grammar isn't simple!).

Passing the result array to an extension function in plain C in order to do the json formatting quickly is hard due to AutoIt using Variant type.

[argumentum]

..basically this is just "SELECT json_group_array( json_object(" & "'name', name, 'etc', etc" & ")) AS json_result FROM (" & $sSQL & ");"
Coding a table_names_generator() can get cumbersome and mainly, slow, just to get the "'name', name, 'etc', etc" part. And that string will be used for every call to the same query.
I love automagical functions but given the time cost of AutoIt executions, in the aim of speed, coding such would defeat the purpose of a speedy script.
The loop I made to take an array and generate a json string was practical but slow, so one has to choose: hard code or magical function. What is faster.

Finding a query that makes the resultant faster, that in itself I'd call "magical query" ( as I did not come up with it solely on my own ), so, I saved around 200 ms. ( in my query ) by having SQLite formatting the return value.

And I feel I know you better than believing that you would aim to belittle anyone. Moreover, you strive to enlighten everyone in every posting, so, thanks for taking the time to evaluate possibilities.

Now, if an "ASM god" comes up with a _SQLite_GetTable2d_array_to_json(), now that would be vary much welcomed