13 ms·
SQLITE: JSON1 Extension
- mablap 10y agoOooh! I just arrived at work and things are calm, this will be interesting reading! We use sqlite for mostly every tool we develop.
- giancarlostoro 10y agoThis is very interesting, I wonder what drove this extension and how things will shape with SQLite. I always loved that in Python I can 'just use' SQLite quite easily.
- mayli 10y agoThe embedded version of mongodb?
- andrewstuart2 10y agoMore like embedded postgres.
- Buttons840 10y ago> The json_object() function currently allows duplicate labels without complaint, though this might change in a future enhancement. Just like Mongo. I was suprised to learn that Mongo can actually store an object with two identical keys. Most drivers will prevent you from doing so, and will fail to display the record fully if it does happen, but it is possible.
- deleted 10y ago[deleted]
- burrows 10y agoHere are instructions for building the extension as a shared library on OS X https://burrows.svbtle.com/build-sqlite-json1-extension-as-shared-library-on-os-x https://burrows.svbtle.com/build-sqlite-json1-extension-as-s.... I wasn't able to get a working build on Windows.
- voltagex_ 10y agoWhat happened on Windows?
- burrows 10y agoI was able to build the dll and load it into python, but using any of the json1 functions caused my python to crash.
- marvel_boy 10y agoThanks for the procedure. Exactly what I needed !
- vhost- 10y agoI always seem to be the black sheep in a group of people when I say that I love sqlite. It's seriously so handy. If I need to aggregate or parse big CSV sheets, I just toss them in a sqlite database and run actual SQL queries against the data and it makes the process much faster and I can give someone their data in relational form on a thumbdrive or in a tar.gz.
- kobeya 10y agoMost people I interact with love sqlite.
- m_fayer 10y agoI thought everyone loved SQLite! It's tiny, no-fuss but full-featured, performs well, and works great as a data-interchange format. I use it in all my simple ad-hoc personal apps, and where would mobile be without it?
- vhost- 10y agoNot to mention browsers. Many people are surprised to learn iMessage, Chrome and Firefox all use SQLite.
- rodgerd 10y agoMore people would probably be surprised to discover Adobe Lightroom catalogues are sqlite databases (which means you can pull all sorts of info out of your catalogue, should you so desire, or fiddle with it in unsupported ways). It's a shame I don't see Adobe on the list of sponsors/contributers, I have to say. Lightroom makes a huge amount of money for them.
- wybiral 10y agoI'm a fan of sqlite for similar reasons. I also use it frequently to do some data massaging before loading into a larger database since it has a standard Python module and queries are quick and easy.
- sbuttgereit 10y ago
- jzelinskie 10y agoIt should be made clear that this functionality is not equivalent to the Postgres JSON support where you can create indexes based on the structure of the JSON. This is an extension to the query language that adds support for encoding/decoding within the context of a query: SELECT DISTINCT user.name FROM user, json_each(user.phone) WHERE json_each.value LIKE '704-%'; It's pretty neat considering they rolled it all on their own: https://www.sqlite.org/src/artifact/552a7d730863419e https://www.sqlite.org/src/artifact/552a7d730863419e PS: If you haven't looked at the SQLite source before, check out their tests.
- brotherjerky 10y agoAnything specific about their tests? I found this doc: http://www.sqlite.org/testing.html http://www.sqlite.org/testing.html
- jncraton 10y agoSQLite is frequently cited for having incredible robustness, and this is certainly related to incredible test coverage. Many of the stats on that page are impressive, but the one that always gets me is that for 122 thousand lines of production code, the project has 90 million lines of tests.
- chrisweekly 10y agoTests are like guard rails. Nobody's saying they shouldn't be there, but they're safety nets. [Rich Hickey's _amazing_ talk on simple vs easy](https://github.com/matthiasn/talk-transcripts/blob/master/Hickey_Rich/SimpleMadeEasy.md https://github.com/matthiasn/talk-transcripts/blob/master/Hi...)
- sAbakumoff 10y agoThough I am a huge fan of SQLite, I am not sure if the incredible test coverage necessarily leads to success : "Trying to improve software quality by increasing the amount of testing is like trying to lose weight by weighing yourself more often. What you eat before you step onto the scale determines how much you will weigh, and the software-development techniques you use determine how many errors testing will find. If you want to lose weight, don't buy a new scale; change your diet. If you want to improve your software, don't just test more; develop better."(McConnell, Steve (2009-11-30). Code Complete (Kindle Location 16276). Microsoft Press. Kindle Edition) Another unique aspect of SQLite code base is total sticking to KISS(http://www.jarchitect.com/Blog/?p=2392 http://www.jarchitect.com/Blog/?p=2392)
- zxv 10y agoIs there any plan to support jsonb (or similar) which would speed processing by eliminating the need to reparse?
- thinknlive 10y agosqlite 'all the things!'. Seriously. One of the best tools ever. So many data, and related performance challenges, in almost any app can be solved efficiently with this (for what it does) tiny little library.
- nattaylor 10y ago>Experiments have been unable to find a binary encoding that is significantly smaller or faster than a plain text encoding. (The present implementation parses JSON text at over 300 MB/s.) I understand that JSONB in Postgres is useful primarily for sorts and indexes. Does SQLite work around this somehow, or is that just not included in their experiments?
- haldean 10y agoLooks like you can't index based on JSON in SQLite, so they might be optimizing for different metrics?
- adsharma 10y agoNot clear if you can compose these functions. Flatbuffers over rocksdb should be considered an alternative. You can then use iterlib to do similar things. Plug: https://github.com/facebookincubator/iterlib https://github.com/facebookincubator/iterlib
- vdm 10y agoThank you for plugging iterlib here, I hadn't heard of it. Have you heard of storehaus? https://github.com/twitter/storehaus https://github.com/twitter/storehaus In terms of the fundamental abstraction offered, it seems comparable to iterlib to me, but I'd love to hear your opinion. (See also: https://upscaledb.com/ https://upscaledb.com/)
- tenken 10y agoIs there a Ubuntu PPA or docker image of SQLite + JSON extension enabled? I can't get the compiled json1.so to load on Ubuntu 14.04 lts with stock SQLite.
- onli 10y agoI think the version on 14.04 is too old. I found the statement you need 3.9 for that (is that somewhere documented properly?). For what is worth, the sqlite3 version in Ubuntu 16.04 has that json1-extension loaded by default: sqlite> CREATE TABLE user (name TEXT, phone TEXT); sqlite> INSERT INTO user VALUES("tester", '{"phone":"704-56"}'); sqlite> SELECT DISTINCT user.name FROM user, json_each(user.phone) WHERE json_each.value LIKE '704-%'; tester I'm now even more happy I upgraded my server to that version yesterday, the server for that project was like yours on 14.04. It was even also because of sqlite, I wanted to have a version that supports the WITH clause. Upgrading to 16.04 (actually, I made a fresh install and moved the data over) seemed like the easiest way to get that.
- Lxr 10y agoWait, what? Isn't storing JSON data as text in a relational DB against all kinds of normalisation rules? Under what circumstances should one do this?
- singingfish 10y agowhen you have arbitrary semi-structured data of limited scope to store. For example the stuff I was working on today is pluggable payment infrastructure. The vendor response is stored json (comes down the wire as xml or json, json is easier, I refuse to anticipate the structure of the data for future providers but I want the whole data returned for debug purposes), and the extra data it requires for transaction resolution is also json. Again I have no idea what this will look like for future payment providers, and this data will not result in consequences for other bits of the database.
- mrcarrot 10y agoYep, I've done something very similar for payment processing (from multiple providers) recently. Have a well defined schema containing the columns that I _know_ I need now for handling a transaction, but also include a jsonb column that stores all of the data that the payment provider provides for the transaction. For one, this makes debugging easier, and it also means that should business needs change in the future and some field that we've been receiving becomes important for payment processing, it can be extracted from the json field and promoted to a column in the table, without having to _now_ define a load of columns for every possible field that every payment provider can ever supply.
- k__ 10y agoSimple example, settings. Stuff you want to make configurable at runtime.
- orf 10y ago> as text That's the issue he was referencing, I think.
- lsaferite 10y ago
- GrumpyNl 10y agoHow does this perform on larger tables?
- ilitirit 10y agoI use this for a custom JSON query tool and browser I wrote for our company (the C# client can load extensions). It's been available for a while. Is this post to spread awareness or have they added something to new to the extension?
- nbevans 10y agoSQLite is a great database for microservices and other minimalist architectures. You'll never get a "TCP socket failed/timeout/reset" from SQLite - that's for sure.