[sqlite] json_* functions in sqlite
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" wrote: > On Tue, 21 Apr 2015 18:09:33 -0700 > Ashish Sharma 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 >
[sqlite] json_* functions in sqlite
On Tue, 21 Apr 2015 18:09:33 -0700 Ashish Sharma 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] json_* functions in sqlite
On Wed, Apr 22, 2015 at 2:17 PM, Eric Rubin-Smith wrote: > > On Tue, Apr 21, 2015 at 9:09 PM, Ashish Sharma > 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 >> > > I wrote a little tool that more or less allows the user to ingest JSON > into an SQLite database, for use in tiny platforms (think a VM running > inside a low-power home router). I was considering open-sourcing it. > > Below is the man page for the tool. If there is enough interest, I'll do > the requisite work to remove dependencies on other bits of my > infrastructure and publish it somewhere. Let me know. > Apologies for self-replying. The formatting of my man page copy-paste looks pretty bad in a lot of places. Here's a PDF-ized version for easier reading: https://www.aterlo.com/wp-content/uploads/2015/04/json2sqlite.pdf Eric
[sqlite] json_* functions in sqlite
On Tue, 21 Apr 2015 18:09:33 -0700 Ashish Sharma wrote: > Hi > > 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 > > I have two questions > > 1. Will sqlite overlords consider adding this to sqlite core? > 2. If so, what steps are needed for this? Check unql.sqlite.org. It's abandonware AFAIK, but you can use part of the code. Stephen Beal colister has cson (http://fossil.wanderinghorse.net/wikis/cson/?page=cson), you can use it too. Also, you can make a module/functions to work with BLOBs and TEXTs. Check how sqlar works (http://www.sqlite.org/sqlar/doc/trunk/README.md) > Thanks > Ashish > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users --- --- Eduardo Morras
[sqlite] json_* functions in sqlite
On Tue, Apr 21, 2015 at 9:09 PM, Ashish Sharma
wrote:
> Hi
>
> 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
>
I wrote a little tool that more or less allows the user to ingest JSON into
an SQLite database, for use in tiny platforms (think a VM running inside a
low-power home router). I was considering open-sourcing it.
Below is the man page for the tool. If there is enough interest, I'll do
the requisite work to remove dependencies on other bits of my
infrastructure and publish it somewhere. Let me know.
Eric
json2sqlite(1) Aterlo Networks, Inc.
json2sqlite(1)
NAME
json2sqlite - convert JSON to SQLite database rows
SYNOPSIS
json2sqlite [OPTION]...
DESCRIPTION
Read arbitrary JSON from a file (or standard input) and emit
some
SQLite rows. The form the rows take is dictated by the options.
The entire run of the program is wrapped in a single SQLite
transaction
that is only committed if we do not encounter any critical errors.
This program's original intent was to be run on small,
low-power
devices. It can be provided a strict memory cap with the -M flag.
Input selection
-i, --input-file=FILE
Read JSON from FILE. If FILE is '-', then read from
standard
input. If no value is passed, then '-' is assumed.
If you want to read from a file whose literal name is '-',
you
can qualify the path to the file, e.g. './-'.
-P, --input-path=JSON_PATH
Specify the path of the object you wish to import.
The root-level JSON element has the path '.'. If the
root-
level element is an object, then JSON_PATH may contain a
hierar-
chy of key names at which the input array can be found.
For
example, if we have JSON {"a": [ ]} then the array
con-
taining can be imported by saying '--input-path .a'.
The
leading dot may be omitted. (This leading-dot syntax is
just
provided to make addressing the root-level object look a
little
nicer on the command-line).
If left unspecified, then '.' (i.e. the whole document)
is
assumed.
-T, --input-type=[array|object]
If 'array' is given, then we import the JSON array that can
be
found at the JSON_PATH specified by --input-path.
When importing an array, it is assumed that each of the
elements
of the array is itself a JSON object. These objects may
either
be a simple name->scalar mapping, or they may have some
more
complex nested form. The objects are usually either imported
as
one row per object, or one row per name/value pair, depending
on
the other arguments to this program.
Otherwise, if 'object' is given, then import a single
JSON
object whose path is the JSON_PATH specified by
--input-path.
The object is imported just as if it were the unique element
in
a containing array and the array had been specified for
import
via --input-type=array.
If left unspecified, then 'array' is assumed.
Output Selection
-o, --output-database=DATABASE_FILENAME
Emit rows to the database file DATABASE_FILENAME. This
argument
is mandatory.
-t, --output-table=TABLE_NAME
Emit rows to the given TABLE_NAME. This argument is
mandatory.
Initialization
-s, --schema-file=SCHEMA_FILE
Run the SQL statements in SCHEMA_FILE before doing
anything
else.
-D, --delete-first
Run 'DELETE FROM ;' before ingesting any
records
(but still within the process's global transaction, so that
sub-
sequent failures will not lead to data loss).
Transforming input to output
Note that the input selection mechanism you chose above with
--input-
type and --input-path has given us a list of objects (where the
list
has length 0, 1, or more). This is called the 'initial result set'
in
the below. The following options instruct json2sqlite how to
convert
each of those objects to some list of table rows. There are two
over-
all structures that each object can have (nested or flat), and
two
overall approaches for converting those structures to SQLite
rows
(either one row per name-value pair or one row per object).
Input structure specification
You must indicate to json2sqlite the structure of the objects in
the
initial result set. If you don't specify one, then --flat is
assumed.
[sqlite] json_* functions in sqlite
I'm no where near the level of an overlord, except maybe to the wifes dog. I'm in a debate mood, so why not? I'm open to the firing squad today. :] IMHO, there are four (I initially started with two) problems with this request in making it part of the core dealings Dr Hipp provides us all. I say this without knowing what the OPs purposes are to have SQLite start understanding what JSON is and how it is handled. Information Exchange Technologies (IET) shouldn't be part of a database language. STORED in a Database, sure, of course, no problem. Technologies like XML, JSON, HTML, BMP, JPG, MP3, TXT, DOC, XLS, and anything else that contains information that is presented AS A WHOLE to a client/recipient is part of the IET group. SQLite isn't part of that group. SQLite is a *organizational *and (hopefully) *organized *storage medium. You put data in, it stores that data. All of it. In one container. It doesn't matter what that data is, it just stores it and knows how to get it when needed. With IETs, all data is exchanged, not just select parts of it. With a DBMS, changing one or two pages in in a terrabyte sized data doesn't cause a terrabyte worth of data to be rewritten when you change a 1 to a 0 or an Z to an F, unlike any IET. I'll cede to that you can open up a hex editor and single byte/char write to change that 1 to a 0, but, change that 1 to a the content of this email thread in an XML file without writing out the whole file again. DBMS shouldn't ever deal with an outside technology other than a Structured Query Language which boils down to a table containing rows and fields. It shouldn't ever need to think about what is in a 'cell' in a table and how to handle it. The outside world says "I want this information, gimme" and the DBMS should get it. The DBMS should only ever play within its own sandbox, and not allow the other kids to play with it. JSON specifically is for client use, configuration use, or rarely changing data in kilobyte chunks of information at most. If you're going to start throwing the overhead of interpreting what JSON is within the DBMS, you might as well just use the DBMS language for better storage and retrieval of information, then have your n-tier application handle the "manglement" of the JSON for whatever you need. Since JSON is setup as a key:value standard (Value being number, string, or array of sorts), you could stretch this into the thought that this is a valid structure to a database. After all, you have a field:value relationship with SQL. However, storing a database within a database seems to be quite a bit redundant to me. XML and JSON should rendered upon output from a database query, and any JSON/XML that is to be put into a database should be dissecting the data outside the database engine with appropriate tables. The exception to this might be for static configuration files based on a users preference, system configuration, etc. But changing data? I'm seriously iffy. It'll eventually get to the point where you'll get into 'database'ception and that is a whole other puddle of mud. Theory would be to make a database to take the stored database to make the change then re-store the database in a cell of a database. Lastly, part of SQLites title says it all. Lite. If every technology were added to handle information exchange, might as well start calling it SQLarge. SQL-Bloat or SQL-Fat just don't have the same ring to it, eh? If JSON gets in, who's to say XML doesn't go in? Or HTML as a query? Or if someone figures out how to convert a PNG to a database structure, why not throw that in? My point here is that IET changes all the time, and it isn't the responsibility of a database engine to keep up which has its own kids to feed. Now, with those four points, that doesn't exclude that the possibility exists to add such functionality. If you use the amalgamation, and you know C, you can always add the additional functionality on your own. IIRC, there are methods to include calls to external modules/DLLs/whatever that you'll have to write on your own, so you may not have to be entirely reliant on C. I've not checked, but there may already be modules that 'plug in' to the SQLite DLL that'll do exactly that JSON interpretation. Myself, if you've not figured out, I feel that a database should store raw information. BMP grade level of storage. Raw, unaltered, factual, bottom line information. Anything that could be considered an interpreted piece of information, such understanding what a PNG is versus a BMP shouldn't be part of the DBMS. It doesn't matter how convinient it may be to have the DBMS understand the difference, the purpose of a DBMS isn't to know, but just acknowledge that something is there (Or not there in the case of NULL) On Tue, Apr 21, 2015 at 9:09 PM, Ashish Sharma wrote: > Hi > > Many times I store JSON data in sqlite. It will be useful if sqlite came > with functions which understand JSON. Presto has a
[sqlite] json_* functions in sqlite
Hi 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 I have two questions 1. Will sqlite overlords consider adding this to sqlite core? 2. If so, what steps are needed for this? Thanks Ashish
