-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 22/04/14 15:00, Neville Dastur wrote:
> On 22 Apr 2014, at 21:58, Roger Binns <rog...@rogerbinns.com> wrote:
>> 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.
Does that mean you can make it individual fields instead of a list? As
Petite Abeille pointed out you need to do SQL things the SQL way or you
will end up in a world of hurt.
>> How much data will there be overall and how performant does all this
>> have to be?
> 10,000s or records
Doesn't sound like much.
>> 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”
You are searching within a column so an index can't be used - (eg looking
for |4444| anywhere within that column). That means every record has to
be examined. Hence my queries about data size and performance requirements.
You will need to normalize if you want SQLite's performance and use
indexes so that columns contain exactly one value.
>> 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"] }
You have a SQL table containing one column which is that entire document
as a single JSON string. In SQL you do SELECT json FROM table WHERE json
LIKE '%4444%'. This will match the above as well as any record where 4444
is in the _id, description etc.
Then in your app code you deserialise the json and check the object for
your exact query. ie you use SQL to make a first pass looking for
candidates, and use your own app specific code to narrow that down.
>> 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.
Really? It is trivial on iOS, and not too hard on Android (need to use
the NDK).
There is no easy way out of this.
Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1
iEYEARECAAYFAlNXEIoACgkQmOOfHg372QQWxgCbBREj/ZCFTmdbFf5fLQLAqkAt
NTUAn0TfMt3T9faOVbfgfGpzIoeg9ndY
=rkUB
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
