7 ms·
JSON and Virtual Columns in SQLite
- AlbertoGP 5y agoConcise and to the point, I’ll keep this around for experimenting with JSON in Sqlite later. I tend to use JSON files for small applications but if using JSON works as well as reported in Sqlite that will be my new go-to even for tiny experiments. I’ve used Sqlite before but I imagined that it would not work so well with JSON.
- jmt_ 5y agoI've been using sqlite w/ JSON columns for doing a series of web scraping projects at work. For each site/service scraped, the general flow is to capture a set of key variables then stash other data in one or more JSON columns. This way I can easily get data my boss is looking for in the short term using the non-JSON columns, then can (usually) easily pull additional information using queries on the JSON columns so I don't need to rescrape the same pages. Then I can create new columns and backfill them using queries against the JSON columns if need be. It's been working amazing for me so far. I will say getting used to the json1 query syntax can be a little confusing at first since it doesn't quite feel like SQL syntax nor dictionary/object sort of syntax -- it's more like jq. So be prepared to sit with the json1 docs + stackoverflow for a little while. But once you get that under your belt, I think you'll be impressed with how quickly you can move with this approach. I also used to use little JSON/CSV files for small projects but after getting comfortable with sqlite + json1 and having many experiences where I end up exporting that data to sqlite anyway, I just go straight to storing everything in sqlite and using JSON columns when I don't have time to design a proper relational database and/or when I want to save JSON to disk in an easily queryable and modifiable fashion. If you're on the Python side of things, check out peewee. It's become my go to ORM and has excellent support for sqlite extensions like json1. You define a variable pointing to your sqlite file, a class for each table, have peewee create the tables, and you're all set
- JamesSwift 5y agoIt works really well. I keep a couple "master" tables and do a bunch of views off those main tables to do ETL reporting for one of my projects. It is performant, and the JSON support has been robust enough for everything I've tried to do.
- jpcapdevila 5y agoWhat's the difference/advantage between defining the virtual column or creating the index directly like this: CREATE INDEX test_idx ON events(JSON_EXTRACT(data, '$.someKey'));
- discodave 5y agoFor a start, the top level of the JSON document could be an array.
- jpcapdevila 5y agoFair, but for objects like in the OP sample? Is there any advantage that justifies the extra steps?
- zrail 5y agoI think the advantage for simple objects is just that you can refer to the columns directly vs having to go through a function. It's only really a big win if you're manually writing a lot of sql or if you're using something like Rails and you want to write queries against values in the JSON blob without going through a bunch of hoops.
- CGamesPlay 5y agoThat doesn't matter. The json_extract function returns a string, so json_extract('[{"foo": "x"}]', '$.foo') is NULL and json_extract('[{"foo": "x"}]', '$[0].foo') is 'x' and it doesn't matter if this is destined to go into a generated column or an index. [append] And SQLite's json_extract doesn't have anything like '$[*].foo' or any mapping functions. You could use json_each to do that, but the process is very different from creating an index.
- jmull 5y agoI think the way the article does it just provides an easy, symbolic way to reference the computed value... so it gives a little readability, it's maybe easier to ensure your where clause and index match up. Also, depending on your access patterns, you might want the generated column to be stored (evaluated on write) rather than virtual (evaluated on read).
- CGamesPlay 5y agoBut if you are going to be querying on a particular, known-ahead-of-time value in the JSON, why did you use JSON to begin with? It's not like the data came through some other system, since this is SQLite. Couldn't you have just extracted the pieces you cared about ahead of time?
- ec109685 5y agoIf you have between 11 and 100 fields and only need to lookup based on 2, it seems like it’s a win?
- deleted 5y ago[deleted]
- nine_k 5y agoWhat if you know a few fields, like some "id" or "name" at the top level, but the rest of the JSON is highly nested, tree-like? Instead of mapping it onto relational tables and keeping indexes, and then running a complex query, you can just put them all into a ready-made, well, blob. Note that it only makes sense to have a relational column if you need to (1) use it as a query condition or (2) update it independently of others. In many tables, many columns are neither, and can be safely stored as JSON.
- alphalima 5y agoExactly the point I was going to make. It's also relatively trivial to generate view statents from the schema of the object if required for analytics (not fast though). Much better than a crazy ORM rat nest in my experience.
- linsomniac 5y agoIn my case, I have a bunch of data in the JSON, but found users need to filter requests based on just one field of the data. In specific, I'm loading Postfix logs into the database, I parse the logs into JSON and then store that JSON in a column. The format of that JSON comes in ~4-5 flavors, depending on the exact log event, but most of them include a "to" field (the envelope recipient), that the API users need to filter on. I ended up adding a "to" column and mirroring the JSON "to" field into that, but this would have worked as well. The API users still have access to that additional rich data, and the whole thing overall is simpler because I don't have it exploded out into a bunch of fields that have many of them NULLs for various log types.
- derpmcterp 5y ago> You decide to store the raw JSON, as normalization is non-trivial. You lost me. If you have to build the index, normalization is immediately trivialized. This is just always a sloppy hack so this advice is just a big “temporary workaround” until normalizing for hot paths.
- jmt_ 5y agoTake various data collection analysis tasks as an example - there's many cases where you may not have a good idea of how everything fits together until you can run queries on the data, with JSON being a very common format. You many may need to combine many JSON datasets, with different layouts and potentially deeply nested structures, as well as more traditional tabular data. Being able to query all of this by putting everything into a temporary sqlite file is incredibly powerful. Sure, you're moving much more into SQL territory once you're creating indexes on JSON data as you say, but I'm not sure that always means "sloppy hack". For data tasks at least, being able to combine the power of relational + noSQL databases is incredibly productive. Doesn't mean I would necessarily put something like this into production, but for figuring stuff out combining the two approaches has a lot of merit in my experience.
- jitl 5y agoEDIT: see my comment here: https://news.ycombinator.com/item?id=31399397 https://news.ycombinator.com/item?id=31399397
- gfody 5y agonormalizing from raw json isn't terribly complicated but it's definitely more complicated than just indexing some paths into it. if the json is loosely defined normalization might not be practical (ie requiring too many tables or needing to be revisited too frequently as the json format evolves too often, etc, etc.)
- tracker1 5y agoThe example I like to give is different payment types... CC/Payflow, Paypal, Amazon Checkout, etc... all you really care about is the type and amount and success... The additional details, different transaction id's etc are different... this allows one to use a single field for the "extra" details, while normalizing a few bits that may come back from different services in a different way, but a single type of collective data. Normalizing the above would mean at least 4 tables, with subqueries and joins that really aren't valueable at the database layer.
- ricardobeat 5y agoIsn’t this going to make writes very slow, since the index will have to update (and parse the value X times) after insertion?
- jitl 5y agoYeah, using expression indexes or generated columns like this is a tradeoff - slower writes for faster queries & more declarative (de)normalization. The same could be said about any index, though… why is this index strategy more concerning? All indexing trades off write speed for read speed.
- keybored 5y agoWhat if you only need to do inserts and never updates?
- remram 5y agoBy "X times" do you mean for each column or when the index is rebuilt?
- simonw 5y agoPaul Ford said something about SQLite a few years ago which really stuck with me: https://simonwillison.net/2018/Jan/29/paul-ford/ https://simonwillison.net/2018/Jan/29/paul-ford/ > The JSON interface is like, “we save the text and when you retrieve it we parse the JSON at several hundred MB/s and let you do path queries against it please stop overthinking it, this is filing cabinet.”
- rockostrich 5y agoThe "this'll work for most use cases" approach to solving problems is great until it isn't, but you most likely have a "good problem" on your hands if you hit that point.
- tiborsaas 5y agoOh, how many times I hoped for having a scaling issue :)
- deleted 5y ago[deleted]
- nalgeon 5y agoNo matter how fast the parsing is — queries are going to be slow. For example, on 1M rows table with very simple flat JSON values like these: {"object": "user", "object_id": 10, "action": "login"} Without index, this query is going to take 500ms or more: select * from events where json_extract(value, '$.object_id') = ?; With index on json_extract(value, '$.object_id'), it is going to take 1ms. See for yourself: https://sqlime.org/#gist:c452550ae694382f32188bc29412b24a https://sqlime.org/#gist:c452550ae694382f32188bc29412b24a
- simonw 5y agoNot disagreeing with you that indexes are a great idea here - but 500ms is fast enough for a LOT of cases.
- 5y ago
- flakiness 5y agoI didn't know about the virtual(generated) columns. It looks kind of a view. Is it part of the SQL standard or is it unique to sqlite?
- nalgeon 5y ago> Virtual columns are defined of SQL:2003 as Generated Column, and are only implemented by some DBMSs, like MariaDB, SQL Server, Oracle, PostgreSQL, SQLite and Firebird. https://en.wikipedia.org/wiki/Virtual_column https://en.wikipedia.org/wiki/Virtual_column
- flakiness 5y agoThanks for tip!
- tracker1 5y agoWorth noting, if you're using JSON data with MS SQL Server, you'll want to create virtual columns for index in a similar way.