5 ms·

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 wor

by 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. : )
- tyingq 11y ago
  "IF you created the ability to store the same database across several files, I would be most grateful to you." http://www.sqlite.org/src/doc/trunk/src/test_multiplex.c http://www.sqlite.org/src/doc/trunk/src/test_multiplex.c
- takeda 11y ago
  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.
azinman2 11y ago
Id like to be able to compose subset JSON objects (give me only the following keys), be able to create indexes on values especially partial indexes where I can say something like x.likes > 5 or the like, some syntax for updating or reasoning about the object that is JSON-patch like. I think in android this could be really important as a way to deflate objs into native types in c-land -- Java parsers on android are so slow that Facebook had to move to flat buffers to avoid them.
karl42 11y ago
I don't care much for json data in tables, but I would really like to use aggregate functions returning json to create hierarchical results from queries with multiple levels of aggregation.
LBarret 11y ago
you cannot go wrong copying postgresql.

by SQLite 11y ago

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. : )

tyingq 11y ago
"IF you created the ability to store the same database across several files, I would be most grateful to you." http://www.sqlite.org/src/doc/trunk/src/test_multiplex.c http://www.sqlite.org/src/doc/trunk/src/test_multiplex.c
takeda 11y ago
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.

azinman2 11y ago

Id like to be able to compose subset JSON objects (give me only the following keys), be able to create indexes on values especially partial indexes where I can say something like x.likes > 5 or the like, some syntax for updating or reasoning about the object that is JSON-patch like. I think in android this could be really important as a way to deflate objs into native types in c-land -- Java parsers on android are so slow that Facebook had to move to flat buffers to avoid them.

karl42 11y ago

I don't care much for json data in tables, but I would really like to use aggregate functions returning json to create hierarchical results from queries with multiple levels of aggregation.

LBarret 11y ago

you cannot go wrong copying postgresql.