4 ms·
wait, are you saying that the columns of view tables could be compressed? that doesn't make sense to me cause views don't use extra space to begin with....? st
by gcr 4y ago
wait, are you saying that the columns of view tables could be compressed? that doesn't make sense to me cause views don't use extra space to begin with....?
storing raw JSON in sqlite means the entire blob would be compressed and live in a single column, right?
- wmanley 4y agoYou'd compress the JSON, and leave the data that you extract from the JSON uncompressed using Generated Columns[1]. That way you can save the original JSON data, but still have fast querying, etc. It would look something like this: CREATE TABLE t1( json TEXT, data INT GENERATED ALWAYS AS (json_extract(body, '$.d')) STORED ); If all you need is fast querying you could make `data` a `VIRTUAL` column and set up an index on it. You can read more about this kind of technique here: https://dgl.cx/2020/06/sqlite-json-support https://dgl.cx/2020/06/sqlite-json-support [1]: https://www.sqlite.org/gencol.html https://www.sqlite.org/gencol.html