BIG news: SQLite looks to be adding JSON support!

edwinsn · 2015-08-23 09:15:49

I'm been wanting an embedded document-based store (don't confuse this with a key-value store) for a long time!

https://www.reddit.com/r/programming/co … n_support/

https://news.ycombinator.com/item?id=10103695

ab · 2015-08-23 10:46:23

This is already available in mORMot, with some additional features.

Just define a variant published field, and store a TDocVariant within.
See http://synopse.info/files/html/Synopse% … l#TITLE_76

We defined some JSON operations from SQLite3 code, just like Sqlite3 core did had in the current trunk, as extension.
See http://synopse.info/files/html/Synopse% … l#TITLE_78

Our implementation is very proven, since it uses the JSON core of mORMot, and is able to handle the "extended" JSON syntax of MongoDB: field names can be serialized without double quotes, which reduces a lot the storage size, and the transmission size, when the JSON is stored within a TEXT column, which would escape all double quote characters.

I guess the performance of our implementation may be faster than current SQlite3's extension.
We use a SAX-like fast approach, without any temporary memory allocation during its search, whereas SQlite3's extension is doing a lot of memory allocations, in some cases.

I've just written a blog article about those features, to show some light on them.
See http://blog.synopse.info/post/2015/08/23/SQLandNoSQL

edwinsn · 2015-08-23 12:25:38

HI Arnaud, before I read through the document you pointed out and the new blog post you just written (I guess it's a reaction of this forum post), I'd like to say that you are awesome, you are so fast!

edwinsn · 2015-08-24 09:34:59

Hi Arnaud,

It seems that the current mORMot implementation only provides functions (which are accessible within a SQL statement) for reading JSON data stored in a DB, I think the SQLITE3's own functions also support updating the JSON data, eg. updating a property.

ab · 2015-08-24 10:15:51

Indeed.

You need to use an UPDATE statement to update a property, with a JsonSet() function.
Could you please create a feature request ticket?

edwinsn · 2015-08-25 06:38:06

@ab, in addtion to updating json properties, we'll also need the ability to add property values.

I think you can investigate the recently added JSON support in postgresql, in order to plan a better support for mORMot.

edwinsn · 2015-08-25 07:10:11

ticket URL: http://synopse.info/fossil/tktview/9085 … 1613916369

ComingNine · 2015-09-18 09:26:45

Dear Arnaud,

Right now I could not find any compilable sample or test concerning the documented JsonGet / JsonSet / JsonHas. I wonder if you could add some ? They would be of great help ! Many thanks !

test_vm1@MICROSO-ABC3D4O /cygdrive/d/Dev/Lib/mORMot
$ find . -name \*.pas -print0 | xargs -0 grep -irw JsonGet
./SQLite3/mORMot.pas:    // - content may be searched using JsonGet/JsonHas SQL functions on a
./SQLite3/mORMot.pas:    // - content may be searched using JsonGet/JsonHas SQL functions on a
./SynSQLite3.pas:begin // JsonGet() would return the raw JSON for Delphi 5
./SynSQLite3.pas:begin // JsonGet(VariantField,'PropName') returns the value of a JSON object
./SynSQLite3.pas:      // JsonGet(VariantField,'Obj1.Obj2.PropName') to search by path
./SynSQLite3.pas:      // JsonGet(VariantField,0) returns the 1st item in the JSON array
./SynSQLite3.pas:      // JsonGet(VariantField,'Prop1,Prop2') returns the values as a JSON object
./SynSQLite3.pas:      // JsonGet(VariantField,'Prop*') returns the values as a JSON object
./SynSQLite3.pas:      // JsonGet(VariantField,'Obj1.Obj2.Prop1,Obj1.Prop2') to search by path
./SynSQLite3.pas:      // JsonGet(VariantField,'Obj1.Obj2.Prop*,Obj1.Prop2') to search by path
./SynSQLite3.pas:  sqlite3.create_function(DB,'JSONGET',2,SQLITE_ANY,nil,InternalJsonGet,nil,nil);

test_vm1@MICROSO-ABC3D4O /cygdrive/d/Dev/Lib/mORMot
$ find . -name \*.pas -print0 | xargs -0 grep -irw JsonSet
./SynSQLite3.pas:begin // JsonSet(VariantField,'PropName','abc') to set a value
./SynSQLite3.pas:      // JsonSet(VariantField,'Obj1.Obj2.PropName','def') to set by path
./SynSQLite3.pas:  sqlite3.create_function(DB,'JSONSET',3,SQLITE_ANY,nil,InternalJsonSet,nil,nil);

test_vm1@MICROSO-ABC3D4O /cygdrive/d/Dev/Lib/mORMot
$ find . -name \*.pas -print0 | xargs -0 grep -irw JsonHas
./SQLite3/mORMot.pas:    // - content may be searched using JsonGet/JsonHas SQL functions on a
./SQLite3/mORMot.pas:    // - content may be searched using JsonGet/JsonHas SQL functions on a
./SynSQLite3.pas:begin // JsonHas(VariantField,'PropName') returns TRUE if matches a JSON object property
./SynSQLite3.pas:      // JsonHas(VariantField,'Obj1.Obj2.PropName') to search by path
./SynSQLite3.pas:      // JsonHas(VariantField,0) returns TRUE if the JSON array has at least one item
./SynSQLite3.pas:  sqlite3.create_function(DB,'JSONHAS',2,SQLITE_ANY,nil,InternalJsonHas,nil,nil);

Last edited by ComingNine (2015-09-18 09:27:40)

edwinsn · 2015-09-23 06:02:09

Update: SQLite has already the json1 extension and it's expected to be integrated into the core once it's stabilized: https://www.sqlite.org/draft/json1.html

Obviously can be very useful for people who need very flexible, even nested database schema.

ab · 2015-09-23 06:43:51

And there are some additional features, very handy not only for JSON process but any other kind of iterative SQL.

See indexes on expressions http://www.sqlite.org/draft/lang_create … #indexexpr
and table-value functions http://www.sqlite.org/draft/vtab.html#tabfunc2

edwinsn · 2015-10-19 16:11:02

Great! Just noticed that the the json1 JSON extension has been integrated into sqlite 3.9.0 official release!

PS, there is a new 3.9.1 which includes a critical fix for a bug related to the newly added JSON features: https://www.sqlite.org/src/info/57eec374ae1d0a1d

Last edited by edwinsn (2015-10-20 06:40:39)

ab · 2015-10-20 07:37:20

I've updated the SQlite3 library.
See http://synopse.info/fossil/info/51b2cabaa9

BTW https://www.sqlite.org/src/info/bc9a9a60c31ebf9b was introduced from a request on our side.

edwinsn · 2015-10-20 11:12:01

Hi Arnaud, You are always fast!

mORMot Open Source

#1 2015-08-23 09:15:49

BIG news: SQLite looks to be adding JSON support!

#2 2015-08-23 10:46:23

Re: BIG news: SQLite looks to be adding JSON support!

#3 2015-08-23 12:25:38

Re: BIG news: SQLite looks to be adding JSON support!

#4 2015-08-24 09:34:59

Re: BIG news: SQLite looks to be adding JSON support!

#5 2015-08-24 10:15:51

Re: BIG news: SQLite looks to be adding JSON support!

#6 2015-08-25 06:38:06

Re: BIG news: SQLite looks to be adding JSON support!

#7 2015-08-25 07:10:11

Re: BIG news: SQLite looks to be adding JSON support!

#8 2015-09-18 09:26:45

Re: BIG news: SQLite looks to be adding JSON support!

#9 2015-09-23 06:02:09

Re: BIG news: SQLite looks to be adding JSON support!

#10 2015-09-23 06:43:51

Re: BIG news: SQLite looks to be adding JSON support!

#11 2015-10-19 16:11:02

Re: BIG news: SQLite looks to be adding JSON support!

#12 2015-10-20 07:37:20

Re: BIG news: SQLite looks to be adding JSON support!

#13 2015-10-20 11:12:01

Re: BIG news: SQLite looks to be adding JSON support!

Board footer