-----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: 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? > 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. If you need performance then you need to normalize the data, or use a database that supports lists like postgres. 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. 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. There was a now defunct project UnQL that was mixing together JSON like data, SQL like queries and bit of SQLite. Roger -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iEYEARECAAYFAlNW19UACgkQmOOfHg372QTNvgCgltU2vNzzVuRfTuPdc2+59VXS h9sAnjya0jcj32aC3nYDA4Myv5x4tyxi =fiQU -----END PGP SIGNATURE----- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
