Hacker News Reader

Comment by wmanley | Hacker News Reader

Unless I'm misunderstanding SQLite can do this as well, but with less nice syntax. The above could be written something like:

    SELECT id, json_extract(data, '$.foo')
    FROM   t
    WHERE  json_extract(data, '$.bar') > 5

in SQLite. You can also add an index on one of these expressions: https://dgl.cx/2020/06/sqlite-json-support

View on HN

It seems like one thing is that Postgres has two types for JSON: JSON and JSONB.

Both validate syntax on insert. The JSONB datatype (not JSON; note the B on the end) further stores the content in a parsed, binary form so future accesses are faster. The schema author does not have to indicate which columns they would like "fast" access to - queries are free to access any arbitrary path in the JSON, and will get reads that don't require JSON parsing.

It seems like SQLite can achieve something similar but not quite the same. It looks like the user has to specifically enumerate and materialize the columns to which they want fast access.

So it seems like json_extract(...) is comparable to Postgres's JSON type, but there's not quite an analagous thing for Postgres's JSONB type.

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

[2] https://www.postgresql.org/docs/9.5/functions-json.html

> 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

> 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.

Reply on news.ycombinator.com