On 22 Apr 2014, at 21:58, Roger Binns <rog...@rogerbinns.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 22/04/14 05:24, Neville Dastur wrote: >> I am looking for some advice on storing and searching data that comes >> from an external JSON source and needs to be stored on device in a >> Sqlite3 database. > > Your data is from MongoDB :) Note they do have an extended JSON to deal > with types like ObjectId, binary and dates: Yes, it is. But I control the document structure and it does not have any types that can’t be converted easily after a pull over the net. > > http://docs.mongodb.org/manual/reference/mongodb-extended-json/#bson-data-types-and-associated-representations > >> On device I need to be able to retrieve the entire “document” and >> display all the array values, > > How much data will there be overall and how performant does all this have > to be? 10,000s or records > >> The options i see are to store the array fields as pipe separated >> values and then use LIKE “|<search_term>%|” to search items. > > That still requires scanning every record as you aren't doing a match from > the start of the value. By this, you mean searching like this is “expensive” > > If you need performance then you need to normalize the data, or use a > database that supports lists like postgres. Not an option as the sqlite bit is on smartphones. > > If you need something for not too much data then just be simple - have a > single column which is the JSON text of the whole document. You can still > use LIKE to find records of interest, deserialise back to JSON and do the > final filtering/querying/sorting there. Not sure how this helps find say 4444 in array2 alone. Note I have changed example so that there is a field value that is the same in array1 and 2 { "_id" : ObjectId(“xxxxxxxxxxxxxx"), "name" : “Description”, “array1" : [ “1111”,”4444” ], “array2" : [ “4444”,”5555”,”6666” ], "tags" : [“searchword1”,”searchword2”,”searchword3"] } > > If you need a "proper" and convenient solution with SQLite then user > defined functions and virtual tables are a great way of hiding what you do > under the hood wrt to how the data is actually stored. You'll have to > write them to understand your data shape and queries. Not really an option as I don’t have that control over the sqlite lib compiled into the mobile app. > > There was a now defunct project UnQL that was mixing together JSON like > data, SQL like queries and bit of SQLite. I am aware. And I recall Richard being involved with this. Neville -- Surgeons Net Education: http://www.surgeons.org.uk Clinical Software Solutions: http://www.clinsoftsolutions.com Find our free and paid apps on the iTunes Apple store and Android Google Play store LinkedIn: http://www.linkedin.com/profile/view?id=49617062 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
