These look like they match what postgres uses? Not sure if postgres invented it, or got it from somewhere else? Time for some specs/standards for JSON operators in SQL?
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.
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.
