Hi all 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.
The JSON records are formatted as (simplified): { "_id" : ObjectId(“xxxxxxxxxxxxxx"), "name" : “Description”, “array1" : [ “1111”,”2222” ], “array2" : [ “4444”,”5555”,”6666” ], "tags" : [“searchword1”,”searchword2”,”searchword3"] } On device I need to be able to retrieve the entire “document” and display all the array values, but I also need to be able to search the arrays and tags. For the array items I will need to be able to search on items just in array1 and for other search types on items in array1 or array2 The options i see are to store the array fields as pipe separated values and then use LIKE “|<search_term>%|” to search items. Alternatively, again store the array as pipe separated, but then also have other tables with columns id, array_item linked back to the main table. Then search using joins. However I can see this getting complicated when it comes to the two different search strategies detailed above. Might be mitigated by having just one extra table e.g. search_helper with id, parent_array, array_item then parent_array could be used to sub-filter. So wondering is anyone that has done this sort of thing and worked out the best way? Thanks Neville _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users