3 ms·

Concise 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 w

Concise 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_ 4y ago
I'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 4y ago
It 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.

jmt_ 4y ago

I'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 4y ago

It 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.