...

или внеочередные заметки


Previous Entry Share Next Entry
SQL/JSON standard-2016 conformance for PostgreSQL, Oracle, SQL Server and MySQL
trekking, Himalaya
obartunov
Updated 21-03-2017:
Refresh data for MySQL ( 8.0.4 version).
SQL-commands, we used for checking comformance

Updated 10-07-2017:
Short version of comparison table.


We compared SQL/JSON Standard-2016 conformance in the latest versions of the major relational databases and it is clearly seen from the table below, that PostgreSQL support is the best ! Nikita Glukhov and I have started this project a year ago, a couple of months after the Standard was published. Our initial intention was to have it in PG 10, but community afraid of the size of the patch. A year of development, reading the Standard, a lot of chatting, were really helpful to us and we confirmed now, that SQL/JSON standard is really useful and our implementation is solid.





We have a lot items in our TODO, for example, Smart indexing of json (PDF).

There is still a chance it will come to PG11, but looking on activity in -hackers I hardly believe to this (I still hope Andrew Dunstan will help, as he did 4 years ago with jsonb) . I hope we will commit SQL/JSON to the Postgres Professional products.


Description of implementation of SQL/JSON in PostgreSQL and our extensions.

Patches on Commitfest:



slon_1.jpg

SQL/JSON feature PostgresSQL 11.0 Oracle 18c MySQL 8.0.4 MS SQL Server 2017 Comments
JSON_OBJECT
JSON_OBJECT(k : v)              
MySQL: JSON_OBJECT(k, v, ...) syntax, duplicate keys removed
JSON_OBJECT(KEY k VALUE v)      
Oracle: duplicate keys removed
JSON_OBJECT(k VALUE v)          
Oracle: duplicate keys removed
JSON_OBJECT(WITH UNIQUE)        
JSON_OBJECT(WITHOUT UNIQUE)     
JSON_OBJECT(ABSENT ON NULL)     
JSON_OBJECT(NULL ON NULL)       
JSON_ARRAY
JSON_ARRAY()                    
MySQL: NULL ON NULL by default
JSON_ARRAY(ABSENT ON NULL)      
JSON_ARRAY(NULL ON NULL)        
JSON_ARRAY(subquery)            
PostgreSQL: FORMAT JSON is not supported
JSON_OBJECTAGG, JSON_ARRAYAGG
JSON_OBJECTAGG()                
MySQL: k, v
JSON_ARRAYAGG()                 
MySQL: NULL ON NULL by default
RETURNING
JSON_CTOR(RETURNING type)       
Oracle: only VARCHAR2, BLOB, and CLOB supported
JSON_CTOR(RETURNING FORMAT)     
JSON_CTOR(ENCODING enc)         
FORMAT JSON
input FORMAT JSON               
Oracle: input JSON is not verified
IS JSON
IS [NOT] JSON                   
PostgreSQL: FORMAT JSON IS JSON not supported
Oracle: in WHERE only
MS SQL: ISJSON() function
MySQL: JSON_VALID() function
IS JSON type                    
JSON_EXISTS
JSON_EXISTS()                   
Oracle: only in WHERE or CASE
MySQL: JSON_CONTAINS_PATH()
JSON_EXISTS(ON ERROR)           
Oracle: UNKNOWN ON ERROR is not supported
JSON_VALUE()                    
MySQL: JSON_EXTRACT() is similar
JSON_VALUE(RETURNING)           
Oracle: only VARCHAR2, NUMBER, SDO_GEOMETRY
JSON_VALUE(ON ERROR/EMPTY)      
Oracle: ERROR ON EMPTY is not handled by ON ERROR, DEFAULT type should match RETURNING type
JSON_QUERY
JSON_QUERY()                    
MySQL: JSON_EXTRACT() is similar
JSON_QUERY(WITH WRAPPER)        
Oracle: scalars without wrapper not supported
JSON_QUERY(ON ERROR/EMPTY)      
JSON_TABLE
JSON_TABLE()                    
MySQL: column's PATH is required, type JSON instead of FORMAT JSON, EXISTS PATH extension
JSON_TABLE nested paths         
Oracle: nested path expressions should be disjunct
JSON_TABLE plans                
JSON path
JSON path basic accessors       
JSON path strict/lax            
Oracle, MySQL: lax only supported
JSON path filters               
Oracle: only one filter at the end and only in JSON_EXISTS
JSON path .*                    
JSON path [*]                   
JSON path [x, y]                
JSON path [x to y]              
JSON path [last]                
MySQL: only "[last]" or "[last - integer_const]" is supported
JSON path [expr]                
JSON path starts_with           
Oracle: only in filter in in JSON_EXISTS
JSON path like_regex            
Oracle: only in filter in in JSON_EXISTS, flags are not supported
JSON path item methods          
Oracle: only one method at the end of path, non-standard names
JSON path .datetime()           
Oracle: non-standard item method names, no datetime formats, can not return datetime type
JSON path arithmetic expressions
JSON path PASSING parameters    
Oracle: only in filter in JSON_EXISTS



Tags: , ,

  • 1
- "JSON path [x, y] [x to y]" and "JSON path [last]" have been available since MySQL 8.0.2 (released on 2017-07-17)

- JSON_TABLE() (incl. support for nested paths) has been available since MySQL 8.0.4 (released on 2018-01-23)

- the alternative to IS JSON in MySQL is the JSON_VALID() function

Спасибо,

идея была проверить соответствие стандарту, это про JSON_VALID. Что касается остального, то мы тестировали 8.0. Сейчас скажу Никите, что надо перепроверить.

MSSQL получил жёлтый крестик за ISJSON() и соответствующий коментарий. Чем JSON_VALID() хуже?

Не знали про json_valid, в следующей редакции поправим. Я болею, Никита болеет.

  • 1
?

Log in

No account? Create an account