

SQLite timeline items related to "json" - coleifer
http://www.sqlite.org/src/timeline?r=json

======
SQLite
The JSON support in SQLite is in a very early stage of design and development
still. There is a lot of cool stuff coming. But there is also still a lot of
work to do.

Please check back in a few weeks.

If you would like to leave comments describing the kinds of problems you would
like JSON support in SQLite to solve, I promise to read them all and give them
careful consideration.

~~~
benten10
Hi, I spend _at least_ $60 x 12 months /year on a NoSQL database that HN
absolutely _loves_ to hate. I don't mind the service, but my first love was
SQLite (thanks to Android, and Python), and my second love was JSON (partly
why I ended up choosing the database, and switched to the NoSQL database).
Some of this is probably slightly off-topic, but since I _love_ the product, I
would very happily use SQLite once again if for:

1) JSON support, as is being offered as described in the post. I want to do
the following in SQLite (look to MongoDB(!) documentation for syntax): a)
Inserts. You know, regular inserts. b) Upserts. The 'hated' database (Mongo,
obvs) has this easily-configurable upsert ['replacements'?], that's incredibly
easy to use. c) Bulk upserts, inserts and deletes.

2) Cheap-ish online database that you can probably use Amazon S3 for, which I
am going to make my institution (University/employer) pay for anyway. Things I
care for are: a) you take care of all the deployment details and provide
support when I need one and b) don't throw me off your service when I cross
your limits because I've overused them because I'm a data-hoarder but I'll
lose my life's worth of work if you throw me off your service.

I would also love it if your offered a choice of setting up couple of indexes
very easily (using JSON, obvs), and offered a visual explorer of data in the
database (relevant because a _certain_ mongo service offers an awesome data
explorer).

Please understand that a very large majority of your users is _NOT_ doing
complex joins or using other advances features of the database. As an
engineer, I would _love_ it if my users used the most advanced features and
let me code the funnest, cutting-edge algorithms, but that's not what I want
as a user. All I ask is simple inserts, deletes, upserts, an online service to
handle them, and an ability to do this all using JSON. This is not going to
make the most exciting days of your work-life, but I would be an extremely
happy person if I were able to use these services.

Sidenote: Since I may never have chance to an SQLite person again, I'll bring
this up even though this is likely WAY off-topic. I am a researcher who likes
to HOARD data. I like storing data in my Uni's server, that has a limit on
file size/total size. _IF_ you created the ability to store the same database
across several files, I would be most grateful to you.

PS: Love your documentation, guys. You really set the bar for the awesomest,
most easily-understandable documentation of extremely technical and advanced
subject matters in the field. All my attempts to make my code readable are
attempts to merely emulate your awesomeness. : )

~~~
takeda
I doubt SQLite can ever be used on S3. There are two problems with that, first
is that to preserve consistency SQLite requires working locking, which S3 by
default can't provide (for sane reason using SQLite on NFS is not advised).

Second problem is that S3 does not allow to modify files, each time you make a
change you need to reupload the whole thing.

I think you might have better luck trying DynamoDB.

Regarding the post, I don't know what to think. The SQLite was banned that way
because it supposed to be light, I feel like adding things like JSON is going
away from that principle.

------
ioddly
Oh hey, neat.

[http://www.sqlite.org/src/artifact/950ed4e8deb8ad4c](http://www.sqlite.org/src/artifact/950ed4e8deb8ad4c)

So from looking at the test, you can get JSON out of select queries now, not a
full blown JSON type like PostgreSQL.

~~~
dfox
The test is written in TCL, which has slightly non-obvious syntax. In essence
it tests SQL functions json_array and json_object which seem to return JSON
arrays and objects as string.

------
jchrisa
If you are interested in mobile JSON databases, we've been working on our
network-enabled native databases for iOS, Android, C# and PhoneGap, since
2010. The library size is less than 500kb and we are compatible with the
Apache CouchDB sync protocol. We also have a server side Sync Gateway that has
fine grained access control, webhooks, etc.

Did I mention everything is open source? There is a mini-hack here that will
let you choose your platform and be up and running in ~20 minutes with what's
basically the stem cell you're gonna be winning hackathons with now that you
know.

[https://github.com/couchbaselabs/mini-
hacks](https://github.com/couchbaselabs/mini-hacks)

Ps we couldn't have done it without SQLite as we started out using that for
our storage engine. Now we are moving to ForestDB because it's more like the
data structures we present in our API.

~~~
nbevans
Seems like a bad place to run a sales pitch.

------
hoodoof
It's a curious thing that Dr Richard Hipp co-created the "unql" specification
then effectively did nothing more with it after that.

[http://www.infoq.com/news/2011/08/UnQL](http://www.infoq.com/news/2011/08/UnQL)

~~~
coleifer
There's code, you can compile it and try it out. For fun I'm thinking to write
a little python wrapper:

[http://unql.sqlite.org/index.html/wiki?name=UnQL](http://unql.sqlite.org/index.html/wiki?name=UnQL)

------
marvel_boy
Newbie here. Can somebody explain me what does the JSON SQL function
extension? Any example?

~~~
dtech
At first glance it seems like some functions that like json_object which can
return (query) results as a JSON array or json_insert to I presume puts JSON
directly into SQL tables.

Nothing too spectacular and nothing you couldn't do with any decent library
already, but some convenience.

~~~
coleifer
Every big feature starts out small.

------
edwinyzh
This is so exciting! To anyone who doubts the value of this effort - do you
have any good option for an embedded, document-based DB system already? Thanks
Dr Richard Hipp!

~~~
coleifer
UnQLite. Not affiliated with SQLite, but its an embedded json document store.
Supports a cool procedural query language.

[http://charlesleifer.com/blog/introduction-to-the-fast-
new-u...](http://charlesleifer.com/blog/introduction-to-the-fast-new-unqlite-
python-bindings/)

------
sbose78
I like it but I am just a bit skeptical about why there is an investment being
done in building this.

Aren't they already too late to the party? I mean, there would have been
tonnes of libraries which would do this given how popular SQLite is. Shouldn't
they have left it to those libraries and stuck to investing on the core
database thingy ( too vague but I hope you get what I mean ).

:)

~~~
coldtea
I'd rather have this built in SQLite, and with its standard code quality and
extensive testing, than have to use some additional library.

And what does "too late to the party" even mean? Who said the party ended or
ever ends?

