For what it's worth, I'd also love some official JSON support, with JSON indexes (eg, a function index that pulls a JSON value). However, I agree it doesn't make sense to add to the main codebase -- I was more thinking an official plugin (so we don't just keep writing our own over and over).
David On Apr 22, 2015 10:57 PM, "James K. Lowden" <jklowden at schemamania.org> wrote: > On Tue, 21 Apr 2015 18:09:33 -0700 > Ashish Sharma <ashishonline at gmail.com> wrote: > > > Many times I store JSON data in sqlite. It will be useful if sqlite > > came with functions which understand JSON. Presto has a nice set > > https://prestodb.io/docs/current/functions/json.html > > In case you don't know, you could implement functions such as Presto > provides yourself with a set of user-defined functions, without any > help from the SQLite project. > > As to whether JSON should be a supported datatype in SQLite, the answer > is clearly No. SQLite, let us note, barely recognizes datatypes at > all, and lacks even a date type. But more than that, JSON as "native" > datatype has two fundamental problems: definition and complexity. > > Definitionally, we should require any datatype have meaningful > operations comparable to those of numbers and strings. What does it > mean to "add" two JSON objects? When is one less than another? Do you > seriously want to propose as a *type* a thing that can't be a primary > key? > > The problem of complexity is that everything in the JSON blob can, > fundamentally, be represented as tables in SQLite. I realize "modern" > tools read/write JSON, that it's the OODBMS of the Javascript set. But > that doesn't change the fact that the JSON tree is a graph, and we know > every graph can be represented with tables. > > Why does that matter? Because a tree-as-datatype would add a whole new > theoretical structure (graph theory) that is 100% redundant to the > relational model embodied in SQLite. You get a bunch of new functions > and ways to get at the data. What you do *not* get is additional query > power. In fact you get less, because graph theory gives you less: no > subsets and no joins, to name just two. > > That's not to say there should be some rule preventing you from storing > JSON in your SQLite database. You may find it convenient, especially if > supported with some functions that make it possible to compare (or > perhaps update) components of it, because it represents some giant > state-property that for most purposes can be treated as an integral > unit. It is to say that every JSON-specific feature you add duplicates > one already present (in a different form) in SQLite. Add enough of > them and you'll replace the DBMS with itself, if you see what I mean. > > --jkl > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
