8 ms·

SQLite timeline items related to "json"

100 points by coleifer 11y ago

ioddly 11y ago
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 11y ago
  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.
- alixaxel 11y ago
  It's pretty straightforward to write a UDF that does exactly that actually, JSON type a-la Postgres would be amazing though. https://gist.github.com/alixaxel/5562152#file-y-php-L212-L231 https://gist.github.com/alixaxel/5562152#file-y-php-L212-L23...
marvel_boy 11y ago
Newbie here. Can somebody explain me what does the JSON SQL function extension? Any example?
- dtech 11y ago
  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 11y ago
    Every big feature starts out small.
SQLite 11y ago
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.
- bliti 11y ago
  Just commenting to say thank you.
- benten10 11y ago
  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. : )
jchrisa 11y ago
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 11y ago
  Seems like a bad place to run a sales pitch.
hoodoof 11y ago
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 11y ago
  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
sbose78 11y ago
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 11y ago
  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?
edwinyzh 11y ago
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 11y ago
  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-unqlite-python-bindings/ http://charlesleifer.com/blog/introduction-to-the-fast-new-u...

100 points by coleifer 11y ago

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 11y ago
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.
alixaxel 11y ago
It's pretty straightforward to write a UDF that does exactly that actually, JSON type a-la Postgres would be amazing though. https://gist.github.com/alixaxel/5562152#file-y-php-L212-L231 https://gist.github.com/alixaxel/5562152#file-y-php-L212-L23...

marvel_boy 11y ago

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

dtech 11y ago
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 11y ago
  Every big feature starts out small.

SQLite 11y ago

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.

bliti 11y ago
Just commenting to say thank you.
benten10 11y ago
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. : )

jchrisa 11y ago

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 11y ago
Seems like a bad place to run a sales pitch.

hoodoof 11y ago

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 11y ago
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

sbose78 11y ago

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 11y ago
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?

edwinyzh 11y ago

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 11y ago
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-unqlite-python-bindings/ http://charlesleifer.com/blog/introduction-to-the-fast-new-u...