SQLite Forum

Proposed JSON enhancements.
Login
Personally I think that Postgres' -> and ->> syntax was a mistake, it confuses too many people because `->'a'->'b'->>'c'` is unnatural compared to `.a.b.c`. In that sense SQLite and MySQL's choice of using json_extract was far more pragmatic, because JSON path resembles something that people know. Postgres 12 added proper JSON path support, and Postgres 14 added new JSON subscripting operators to make JSON access even more familiar (you can see people complaining about the old Postgres -> and ->> syntax in these threads [https://news.ycombinator.com/item?id=27247621](https://news.ycombinator.com/item?id=27247621) [https://www.reddit.com/r/PostgreSQL/comments/nq0p1k/better_json_in_postgres_with_postgresql_14/](https://www.reddit.com/r/PostgreSQL/comments/nq0p1k/better_json_in_postgres_with_postgresql_14/)).

The biggest benefit to adding the new -> and ->> operators is that existing Postgres or MySQL JSON queries will just work without modification in SQLite, but that is not always the case due to the semantic mismatch between ->/->> in MySQL/Postgres and json_nextract/json_extract in SQLite. What about decoupling -> and ->> from json_nextract and json_extract and directly emulating Postgres and MySQL behaviour? Something like

```
sqlite> SELECT '{"a":{"b":{"c":"value"}}}'->'a'->'b'->'c';
"value"

sqlite> SELECT '{"a":{"b":{"c":"value"}}}'->'a'->'b'->>'c';
value

sqlite> SELECT '{"a":{"b":{"c":"value"}}}'->'$.a.b.c';
"value"

sqlite> SELECT '{"a":{"b":{"c":"value"}}}'->>'$.a.b.c';
value

sqlite> SELECT json_extract('{"a":{"b":{"c":"value"}}}', '$.a.b.c');
value

sqlite> SELECT json_extract('{"a":{"b":{"c":"value', '$.a.b.c');
malformed JSON

sqlite> SELECT json_nextract('{"a":{"b":{"c":"value', '$.a.b.c');
NULL
```