4 ms·

Could you expand on what Postgres offers that SQLite doesn't in this regard? I'm curious to know what I'm missing out on.

acatton 5y ago
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 https://www.postgresql.org/docs/9.5/functions-json.html
- wmanley 5y ago
  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 https://dgl.cx/2020/06/sqlite-json-support
  - 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.
  - cldellow 5y ago
    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.
IceWreck 5y ago
Postgres's JSONB type can essentially turn Postgres into a full NoSQL database. Infact, its so good that it can beat MongoDB at its own game in a lot of benchmarks. https://www.theguardian.com/info/2018/nov/30/bye-bye-mongo-hello-postgres https://www.theguardian.com/info/2018/nov/30/bye-bye-mongo-h... Example: The Guardian switched from Mongo to Postgres jsonb as a nosql db.

acatton 5y ago

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 https://www.postgresql.org/docs/9.5/functions-json.html

wmanley 5y ago
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 https://dgl.cx/2020/06/sqlite-json-support
- 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.
- cldellow 5y ago
  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.

IceWreck 5y ago

Postgres's JSONB type can essentially turn Postgres into a full NoSQL database. Infact, its so good that it can beat MongoDB at its own game in a lot of benchmarks. https://www.theguardian.com/info/2018/nov/30/bye-bye-mongo-hello-postgres https://www.theguardian.com/info/2018/nov/30/bye-bye-mongo-h... Example: The Guardian switched from Mongo to Postgres jsonb as a nosql db.