JSON with Sqlite

I've been using the JSON1 extension for some time now (in production projects) and it's truly remarkable. I usually just dump the JSON-response data from an API to a "raw_data" table (typically one "updated_at" column and a second "json_data" one). At that point you can somehow normalize your schema, but only if you really have to! That is because you can get away with a NoSQL-like denormalized schema performance wise, by carefully defining index on expressions. You can somehow normalize…

It 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 PS: If you haven't looked at the…

The cool thing for working with json is to store each json document as is in one column, then make virtual columns that store some specific information you want to query, using some combination of json_extract, then index those columns. This makes for super-fast search, and the best part is you don't have to choose what to index at insert time; you can always make more virtual columns when you need them. (You can still also search non-indexed, raw json…