JSON & SQLite

[JTaylor]

Just ran across something really cool and it ties into the JSON discussion and my previous attempts at a JSON extender.

There is an extension for SQLite which allows for SQL queries against JSON and it works really well.   I want make this part of my SQLite Extender so we can have easy access to JSON capabilities in a somewhat native WinBatch fashion.

My problem is I am not making much progress due to my ignorance of what is needed in Visual Studio to make this compile and load properly.   I am uncertain if I have to create a separate DLL/LIB to do this or if, ideally, I can include this all in the Extender DLL itself and still reference the entry point.    I would like to make use of the sqlite3_auto_extension() option.   I do what I think should work and it compiles fine but it never gives me access to the functionality.   It talks about statically linking and using a -DSQLITE_CORE parameter but, again, not sure if this has to be external or if the static linking is even needed.


https://sqlite.org/loadext.html

If anyone is interested in this functionality and understands what is needed from the above link some pointers would be appreciated.   My entry point is "sqlite3_json_init"

Thanks.

Jim

[stanl]

 That would be quite the contribution

[JTaylor]

I got it figured out.   Now to see what can be done with it...

Jim

[JTaylor]

You probably saw it but if not, I did get the JSON stuff working.   Still contemplating how to make it smoother but with a little creativity you can make use of most all the functionality that the sqlite extension provides.  I posted it under the SQL Beta thread.

Jim

[stanl]

This is good stuff. Nice job.  Have to admit I stumbled a bit trying to organize key, value, obj, array but this does represent a holy grail for Json parsing in pure WB.
********************

[JTaylor]

Thanks.   What they did is very nice.   Glad I stumbled upon it and that it folded into what I already did so nicely.

I agree it does take a little bit to follow what they are doing.  It helps to run a Select *  without a Where statement, in something like DB Browser so you can see how they organize json_tree().   That helps a lot with understanding how to best make use of things.


Jim

[stanl]

If if you traverse the thread where the Op wanted help with yahoo finance, you will see I set out some Json stuff and the bar-delimted output is a basic tree.


But, as you might know, everyone wants stuff in Excel, so parsing [using the yahoo multi-stock example] into an Excel Pivot Table is a goal for other Json beyond Yahoo. Right now that can be done with Power Query / DAX / and R but those don't fit into a WB executable as easily as your Extender or MsScriptControl.

[JTaylor]

You may have already created one but I posted an example in that thread, if not.

Jim

[JTaylor]

Question for interested parties...

Do you see any real advantage of

   

Code (winbatch) Select Expand

len = dbjLength(db,json,'$.books')

Over

Code (winbatch) Select Expand

    len = dbQueryValue(db,"Select json_array_length('":json:"','$.books')")

Trying to decide if such things are worth the effort.   While it might make some tasks slightly easier it would limit the flexibility and one would have to go back to the latter anyway for more involved queries.  Plus it might limit creative thinking by not seeing what is really going on and thereby not consider other possibilities.

Jim