Horrific confession time: I have several .sqlite files that exceed 1.5TB in size and consist of a single table with a single field called JSON (guess what it contains!).
Early days, I just used 'JSON LIKE '%json-substring-match%' for queries, and that did get a bit slow after a while, but mostly the syntax was just really gross when doing aggregations. Fortunately, these days, you can do:
CREATE INDEX IDX_Foo_Bar ON Foo (json_extract(JSON, '$.Bar'));
...which makes subsequent SELECT queries on that function nice and quick again.
This has been going on since (checks create time on one .sqlite file) July 2018, with pretty much zero perf or downtime issues.
Comments
Horrific confession time: I have several .sqlite files that exceed 1.5TB in size and consist of a single table with a single field called JSON (guess what it contains!).
Early days, I just used 'JSON LIKE '%json-substring-match%' for queries, and that did get a bit slow after a while, but mostly the syntax was just really gross when doing aggregations. Fortunately, these days, you can do:
...which makes subsequent SELECT queries on that function nice and quick again.This has been going on since (checks create time on one .sqlite file) July 2018, with pretty much zero perf or downtime issues.