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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
