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