> On Mar 15, 2018, at 11:27 AM, Jay Kreibich <[email protected]> wrote: > > Recognize the fact that if you’re storing data in a JSON string, to the > database that is just one single value: a string.
I am very well aware of that. What _I_ (and my co-workers) are implementing is a higher-level database layer* that _does_ store its data as JSON. > The database has no knowledge and understanding of that value beyond the > fact it is a string. Asking the database to index or do something with the > arbitrarily constructed sub-values you’ve created is outside the scope of > what the database can do, because you’re storing data in a format outside the > scope of the database. You may not be aware of SQLite's JSON1 extension? It does enable exactly this — you can easily index a property of a JSON value, for example, by creating an index on a json_extract() expression that retrieves a property from the JSON column. > If you do want to use database functions and queries to deal with this kind > of thing, store the data in a way the database understands it and can use it… > for example, a table that includes “patient_id, timestamp, temp” with “temp” > being a SINGLE numeric value. Couchbase's users/customers do not store their data this way; they store it as JSON. Making this data queryable on mobile devices via SQLite is my job :) It’s working quite well, but it currently lacks the ability to do fast (indexed) searches over the contents of arrays and objects. Doing so will require some kind of transformation like what you’re talking about, but the JSON is still the source of truth, so the transformed tables need to mirror that. > Don’t have a “source table.” It’s a very non-relational way to store data > anyways. You say that like it’s a bad thing … Non-relational databases** are rather a big deal currently. Implementing one atop a relational database does sound a bit odd, but it actually makes a lot of sense for our use case. —Jens * https://www.couchbase.com/products/mobile ** https://en.wikipedia.org/wiki/NoSQL _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
