4 ms·

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

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?

simonw 5y ago
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.
- xtreak29 5y ago
  Discussion: https://sqlite.org/forum/forumpost/d61880668db1a8f4 https://sqlite.org/forum/forumpost/d61880668db1a8f4
  - jrochkind1 5y ago
    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 https://sqlite.org/forum/forumpost/a0d681bb2432eebc
    - bokwoon 5y ago
      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.
      - jrochkind1 5y ago
        Nice, thanks!
      - jrochkind1 5y ago
        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-functions.html#operator_json-column-path https://dev.mysql.com/doc/refman/5.7/en/json-search-function...
- jrochkind1 5y ago
  I didn't even KNOW about that new syntax in postgres! I'll be using it now that you've pointed it out, thanks.
deleted 5y ago
[deleted]

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?

simonw 5y ago

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.

xtreak29 5y ago
Discussion: https://sqlite.org/forum/forumpost/d61880668db1a8f4 https://sqlite.org/forum/forumpost/d61880668db1a8f4
- jrochkind1 5y ago
  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 https://sqlite.org/forum/forumpost/a0d681bb2432eebc
  - bokwoon 5y ago
    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.
    - jrochkind1 5y ago
      Nice, thanks!
    - jrochkind1 5y ago
      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-functions.html#operator_json-column-path https://dev.mysql.com/doc/refman/5.7/en/json-search-function...
jrochkind1 5y ago
I didn't even KNOW about that new syntax in postgres! I'll be using it now that you've pointed it out, thanks.

deleted 5y ago

[deleted]