"body" is the "json in its own column" you're asking for. They are doing exactly what you're saying.
replyThe point is exactly that it means you can selectively retrospectively add virtual columns, optionally backed with an index, as you decide which fields you need more structured access to.
The example you're giving is in principle the same as the "ALTER TABLE ... GENERATED ALWAYS AS ... VIRTUAL" example + a subsequent index in the Sqlite example.
Postgres does even better and it's available right now. No virtual column needed.
reply CREATE TABLE t1 (data JSONB);
INSERT INTO t1 VALUES ('{"column1":1234}');
CREATE INDEX t1column1 ON t1(data->'column1');
SELECT * FROM t1 WHERE data1->'column1' = '1234'; // not sure about data typeI would not call an index format which gets slower with growth "better". And that vacuum issue on JSONB scales with your data size.
replyWhat do you mean slower with growth? What vacuum issue?
reply