Elena Stepanova added a comment - 2016-12-13 10:49 - edited serg , Have you approved this? What compatibility are we talking about? We cannot use type JSON and don't worry about compatibility, but suddenly for CAST it becomes so important that we fake it (of course, again, without any warnings which a sensible admin could have converted to errrors)? MariaDB 10.2 MariaDB [test]> create table t1 as select cast ( '{"a":"b"}' as json) as a; Query OK, 1 row affected (0.45 sec) Records: 1 Duplicates: 0 Warnings: 0   MariaDB [test]> show create table t1; + -------+-----------------------------------------------------------------------------------------+ | Table | Create Table | + -------+-----------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `a` varbinary(27) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | + -------+-----------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)   MariaDB [test]> insert into t1 values ( 'a' ); Query OK, 1 row affected (0.07 sec) How does it make any sense at all, and in what way it's compatible with MySQL? MySQL 5.7 MySQL [test]> create table t1 as select cast ( '{"a":"b"}' as json) as a; Query OK, 1 row affected (0.65 sec) Records: 1 Duplicates: 0 Warnings: 0   MySQL [test]> show create table t1 \G *************************** 1. row *************************** Table : t1 Create Table : CREATE TABLE `t1` ( `a` json DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)   MySQL [test]> insert into t1 values ( 'a' ); ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column 't1.a' . More about "compatibility": MySQL 5.7 MySQL [test]> select CAST (ST_GeomFromText( 'POINT(1 1)' ) AS JSON); + ---------------------------------------------+ | CAST (ST_GeomFromText( 'POINT(1 1)' ) AS JSON) | + ---------------------------------------------+ | { "type" : "Point" , "coordinates" : [1, 1]} | + ---------------------------------------------+ 1 row in set (0.24 sec) MariaDB 10.2 MariaDB [test]> select CAST (ST_GeomFromText( 'POINT(1 1)' ) AS JSON); + ---------------------------------------------+ | CAST (ST_GeomFromText( 'POINT(1 1)' ) AS JSON) | + ---------------------------------------------+ |

�? �? | + ---------------------------------------------+ 1 row in set (0.00 sec)

Sergei Golubchik added a comment - 2016-12-14 19:56 This pseudo-cast is for cases like select json_object( 'a' , '{"b": "abcd"}' ); What should be the result be, { "a" : '{"b": "abcd"}' } or { "a" : { "b" : "abcd" } } That is, what is the second argument of json_object — a string or a json? This pseudo-cast solves this. Although I would prefer a standard solution instead: select json_object( 'a' , '{"b": "abcd"}' FORMAT JSON);

Elena Stepanova added a comment - 2016-12-14 21:05 serg , so, why not reserve the "room" for the standard FORMAT JSON if there is no time to implement it, and in the spirit of our current endeavor to follow standard defaults , imply the default, which is, coincidentally, FORMAT JSON ? It will be different from MySQL, but at least it won't be difficult to explain (assuming it's documented), and for once it will be better than MySQL (and MariaDB) does now, by returning the first variant, only in an uglier form of MariaDB [test]> select json_object( 'a' , '{"b": "abcd"}' ); + -----------------------------------+ | json_object( 'a' , '{"b": "abcd"}' ) | + -----------------------------------+ | { "a" : "{\"b\": \"abcd\"}" } | + -----------------------------------+ 1 row in set (0.00 sec) Fake CAST goes against everything, not just common sense – it does not improve compatibility with MySQL because results and types are different anyway; it falls out of the standard which does not suggest any explicit CAST there; and it contradicts the definition of the function CAST itself, which is supposed to produce a result value of the specified type (not shape or format).

Sergei Golubchik added a comment - 2016-12-18 13:57 I agree, I don't like CAST. The standard specifies that if FORMAT JSON is not specified, then the second argument of json_object is a string, unless it's an output of another function that returns JSON. So, I'd rather implement FORMAT JSON now. This is, basically, just a syntax sugar.

Alexey Botchkov added a comment - 2017-02-14 10:50 http://lists.askmonty.org/pipermail/commits/2017-February/010687.html