Incidentally, I just added this last night to a project I'm working on --
my first custom function! Once again I'm impressed with the power of
sqlite as I went from concept to working implementation in just 2 hours.
I'm using a JavaScript-style query syntax, where you just directly
dereference the object attributes, and index in with an array. So a query
on a simple object looks like this:
Query: SELECT extractJSONValue( '{"foo":"bar"}', '.foo' );
bar
This works for nested objects and arrays too:
Query: SELECT extractJSONValue( '{"foo":[1,{"bar":2},3]}',
'.foo[1].bar' );
2
And it works inside aggregate functions:
Query: CREATE TABLE testData ( json TEXT );
Query: INSERT INTO testData VALUES ( '{"foo":1}' );
Query: INSERT INTO testData VALUES ( '{"foo":2}' );
Query: INSERT INTO testData VALUES ( '{"foo":3}' );
Query: SELECT SUM( extractJSONValue( json, '.foo' ) ) FROM testData;
6
Anyway, next up I'd like to start indexing on JSON data. It looks like I
could do this with a custom COLLATE sequence [1] and then use that function
in an index [2]. Granted, I'll need to create a new collation sequence for
each item I'd like to index (eg, if I have an object {"foo":1,"bar":2} and
want to index on both attributes, I'll need to create two separate
collation sequences). But I'm thinking I can just create a custom function
create new collation functions on the fly:
SELECT createNewJSONCollationFunction( "FooCollationSequence", "foo" );
SELECT createNewJSONCollationFunction( "BarCollationSequence", "bar" );
CREATE INDEX ON testDataFoo ( "json" FooCollationSequence );
CREATE INDEX ON testDataBar ( "json" BarCollationSequence );
Before giving that a shot, however, I'm hoping someone here can give me a
sense of whether that'll work? Specifically, if I'm generating these
dynamic collation sequences on the fly, how does sqlite store the index on
disk? For example, after creating the index and shutting down the database
handle, how does that index work the next time I load the database --
before dynamically re-initializing the collation sequence?
Anyway, any thoughts on this would be appreciated. Thanks!
-david
[1] https://www.sqlite.org/c3ref/create_collation.html
[2]
http://stackoverflow.com/questions/20942566/indexes-with-custom-collations-in-sqlite
On Fri, Jan 2, 2015 at 1:47 PM, Philippe Riand <p...@riand.com> wrote:
> We’ll probably look at providing a set of functions for handling JSON in
> SQLite, similarly to what POSTGRESQL is doing. But, to make it efficient,
> we need to index the JSON content. I suggested earlier this year to get
> expression based indexes, so one can index the result of a function like
> json_path(“my column”,”a.b.c”) to speed up the search.
>
> That would be my own, single item, wish list :-) It can definitively open
> many doors.
>
> Happy new year, and I wish you guys a great 2015 year!
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
