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
