I've used json1 in SQLite for a bunch of different things and it's great - super-fast, has all of the features that I need. Since it's part of SQLite core I trust it as having the same level of reliability as the rest of SQLite.
A bit more literature on the N+1 situation in SQLite: https://www.sqlite.org/np1queryprob.html
Regarding Postgres vs SQLite, I've found that I can use much simpler SQL calls with embedded databases when I don't need to worry about N+1 query performance issues. That makes many of the query features moot. That being said, there is a JSON extension for SQLite[2] although I haven't tried it.
[1] https://github.com/benbjohnson/litestream/issues/41
[2] https://www.sqlite.org/json1.html