Hacker News Reader

Comment by acatton | Hacker News Reader

Not GP, but PostgreSQL offers jsonb fields[1]

Since PostgreSQL allows for functional indexes, you can query and index the data in a structured way.

For example you have a table t with "id" and "data" where data is a jsonb field like {foo: ..., bar: ...}. You can do

    SELECT id, data->'foo'
    FROM   t
    WHERE  data->'bar' > 5

Which will yield the value of "foo" (inside the json field) for rows where "bar" (inside the json field) is greater than 5.

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

View on HN

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

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.

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.

Reply on news.ycombinator.com