Yeah, the SQLite team cited PostgreSQL as the inspiration here.

I asked them if they'd considered the most recent syntax added by PostgreSQL - "where jsoncol["prop"] = 'x'" - but sadly that was incompatible with other existing SQLite syntax.

Discussion: https://sqlite.org/forum/forumpost/d61880668db1a8f4

One comment there points out some differences with the operators in pg, which I don't entirely follow honestly.

https://sqlite.org/forum/forumpost/a0d681bb2432eebc

hi I was the one who made that comment. The JSON incompatibilities I was referring to at that time was for an older version of the JSON proposal. The current version of the JSON operator in SQLite mimics Postgres (and MySQL) perfectly and I'm very happy about that.

In the older version of the proposal, -> was identical to ->> except -> returned NULL on malformed JSON (while ->> raised an error). Both -> and ->> would automatically convert a JSON-encoded SQL string '"like this"' into an SQL string 'like this'. This is not how the -> operator behaves in Postgres and MySQL, and my examples were simply trying to point out that incompatibility.

I also didn't realize MySQL supported these JSON query operators now, huh. Since MySQL 2.7.9 in 2015 apparently? Not that new!

https://dev.mysql.com/doc/refman/5.7/en/json-search-function...

Nice, thanks!

I didn't even KNOW about that new syntax in postgres! I'll be using it now that you've pointed it out, thanks.