An idea I?ve had a while ago was to implement functions for json documents
(using sqlite3_create_function_v2)
Json would be stored in a text column. You would need to define 2 functions:
- docget(document,key1,subval1,subval2,?)
- docset(document,key1,subval1,subval2,..,val)
Last parameter of docset is value you wish to set.
So for instance one would write
INSERT INTO mytable VALUES (1,?{key : 10, subdoc : {subkey : ?a"}}?);
SELECT id,doc FROM mytable WHERE docget(doc,?key") > 10;
SELECT id,doc FROM mytable WHERE docget(doc,?key?,?subdoc?,?subkey?) = ?a?;
UPDATE doc FROM mytable WHERE id=1 SET docset(doc,?key?,12);
One could even implement indexes on keys within documents using additional
tables.
What do you guys think? Is it stupid, could it be improved?
Sergej
