Hacker News Reader

Comment by inferiorhuman | Hacker News Reader

Most of Postgres is standard SQL. It's just that most non-Postgres databases do not implement standard SQL very well.

Sure, but the non-standard enhancements like JSON support are part of what sets Postgres apart from the competition IMO.

View on HN

SQL:2016 includes support for JSON: https://en.wikipedia.org/wiki/SQL:2016

Postgres 12, whose development has reached feature freeze and is in a stability period, has added some support for JSON path as of SQL:2016: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

The release is planned for roughly next September/October.

Interesting, I wonder which databases actually implement SQL:2016 (and JSON as standardized). JSON support in Postgres predates SQL:2016 in any case.

I think Oracle does. My impression is that PostgreSQL sort-of "invented" JSON support in the db, then Oracle (and probably others) added JSON support with a different syntax, and then Oracle got their version defined into the SQL standard. I'm half-guessing here, but it's based on what I've gleaned from Markus Winand's [0] excellent compatibility tables in his slides [1].

[0] https://modern-sql.com/

[1] https://modern-sql.com/slides/ModernSQL-2019-05-08.pdf, for example

(Edited for formatting.)

Wait, what major relational databases don't support JSON? Oracle and Sql Server definitely do. Does MySQL not support JSON?

SQLite (the most popular RDBMS today) doesn't, unless you count extensions. And of course, none of them use the same syntax or functions or data types.

While it is technically an optional extension in sqlite, it is part of the main source distribution, and very easy to enable.

And why wouldn't you count SQLite extensions? Its JSON1 extension is practically ubiquitous.

If I recall correctly, Oracle's support for JSON is not at the same level as PostgreSQL. By that I mean seamlessly store JSON as a data type like any other. In Oracle JSON is stored as VARCHAR/CLOB and then you implement tests to validate whereas in PostgreSQL and MSSQL is its own data type.

Similar story with SQL Server. I believe JSON support is a bit better in SQL Server 2019, but still far lacking behind that of Postgres.

Hi, MySQL supports JSON (it has a native JSON datatype) but it also support NoSQL (CRUD operations). For more details, check https://www.slideshare.net/lefred.descamps/pyconx-python-mys... or https://www.slideshare.net/lefred.descamps/oracle-code-roma-...

Doesn't every major RDBMS support JSON out of the box these days?

(Last I checked atleast) JSON isn't as well supported on MySQl or MSSQL compared to PG.

In PG, a JSON column is so well integrated that you can do all sorts of crazy stuff (indices over JSON queries is my favorite). You could build an entire RDBMS on top of PG's JSON column.

MSSQL indexes and provides dot notation/object query from 2016 forward, schemas are supported as well.

Reply on news.ycombinator.com