JSON support in SQL Standard, PostreSQL, and MySQL

Question

I have been trying to gather some information regarding JSON support in SQL but Google doesn't help much. The more I search the more confused I become. Here are my simple questions:

• Does the current or upcoming SQL standard define support for JSON storage and manipulation?
• If the answer to the above is yes, where can I find a document (even a draft) that describes the SQL/JSON specification?
• It appears that several databases (SQL Server, Oracle, PostreSQL, MySQL) support JSON but their functions/queries seem different in many cases: https://blogs.oracle.com/svetasmirnova/entry/mysql_and_postgresql_json_functions Which of these databases implement the SQL/JSON standard (current or draft, if there is one)?

Btw, I am mostly interested in PostreSQL and MySQL JSON support.

Thank you in advance.

Answer 1

There is a draft standard for SQL/JSON but it is still not publicly available. It is mostly driven by Oracle so they are aligned with the standard. SQL Server is similar with some syntax/naming changes (e.g. instead of JSON_TABLE it uses OPENJSON syntax but functionality is similar). Oracle/MSSQL use JSON as text representation, PostgreSQL/MySQL have types. You can see overview of functions here:

http://ocelot.ca/blog/blog/2015/11/26/standard-sqljson-and-mysql-5-7-json/

Answer 2

It is now supported by the majors DBMS:

• mysql: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=591207ceddca6fe4f11a3ad8589e7c5b

• sqlserver: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=5c70245f5bb7c96d4adcc6b10ebed195

• postgres: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=0914b4d9622b28f813eed51bab263203

• sqlite: https://www.db-fiddle.com/f/tMcVDVmjxb5wSjemg34dy8/2

Answer 3

JSON data type available is now supported in MySQL 5.7.8. It is very clearly documented: https://dev.mysql.com/doc/refman/5.7/en/json.html