4 ms·
That is true, but SQLite doesn't support functional indexing. This means that you cannot do CREATE INDEX idx_t_bar ON t(json_extract(data, '$.bar')); for
by acatton 5y ago
That is true, but SQLite doesn't support functional indexing. This means that you cannot do
CREATE INDEX idx_t_bar ON t(json_extract(data, '$.bar'));
for fast lookups. But you can do
CREATE INDEX idx_t_bar ON t(data->'bar');
in PostgreSQL. You have some workarounds like I explained.[1]
Also PostgreSQL has many operators[2] which makes using JSON fields easier.
[1] https://news.ycombinator.com/item?id=29461926 https://news.ycombinator.com/item?id=29461926
[2] https://www.postgresql.org/docs/9.5/functions-json.html https://www.postgresql.org/docs/9.5/functions-json.html
- wmanley 5y ago> SQLite doesn't support functional indexing SQLite has "Indexes On Expressions"[1] since 2015. I'm assuming functional indexing in Postgres is the same. So I think the syntax would be: CREATE INDEX idx_t_bar ON json_extract(data, '$.bar'); [1]: https://www.sqlite.org/expridx.html https://www.sqlite.org/expridx.html > Also PostgreSQL has many operators[2] which makes using JSON fields easier. This does look a little more convenient to write than SQLite's JSON syntax, but I don't see any fundamental difference here.