On Apr 22, 2014, at 2:24 PM, Neville Dastur <nevillebdas...@gmail.com> wrote:
> So wondering is anyone that has done this sort of thing and worked out the > best way? Yes. Normalize your data. And that’s that: http://www.schemamania.org/sql/#lists Quoting a few words: "Questions are frequently asked about table designs that are hopelessly wrong. The solution to the question is not to write the query, but to re-write the table, after which the query will practically write itself. Perhaps the most egregious example is a column whose value is a list or, in SQL terms, a repeating group. The elements in the list are perhaps comma-separated, and some poor schlep has the task of selecting or joining on the the nth element in the list.” Don’t be *that* schlep. But, if you like pain and suffering, medieval style, I got a hack for you involving virtual tables, full text search, contentless tables, and shadows even! It goes a bit like this: sqlite> .head on sqlite> select * from json_text where array1 match '1111'; array1|array2|tags 1111,2222|4444,5555,6666|searchword1,searchword2,searchword3 So, what’s json_text? A virtual, contentless, FTS4 table: create virtual table json_text using fts4 ( content='json', array1 text, array2 text, tags text ); Where does it get its content? >From a regular table: create table json ( _id text, name text, array1 text, array2 text, tags text ); insert into json ( _id, name, array1, array2, tags ) values ( 'xxxxxxxxxxxxxx', 'Description', '1111,2222', '4444,5555,6666', 'searchword1,searchword2,searchword3' ); insert into json ( _id, name, array1, array2, tags ) values ( 'yyyyyyyyyyyyyyy', 'Description', 'aaaa,bbbb', 'cccc,dddd,eeee', 'tag1,tag2,tag3' ); See the doc for details about the full setup: https://www.sqlite.org/fts3.html#section_6_2_1 What about the shadows you may ask? Here you go: create virtual table if not exists json_term using fts4aux( json_text ); sqlite> select * from json_term order by 1 limit 10; term|col|documents|occurrences 1111|*|1|1 1111|0|1|1 2222|*|1|1 2222|0|1|1 4444|*|1|1 4444|1|1|1 5555|*|1|1 5555|1|1|1 6666|*|1|1 6666|1|1|1 All the text fields, nicely tokenized, and for your to use and abuse. More info: https://www.sqlite.org/fts3.html#fts4aux _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
