Run the following query on a fresh installation of MariaDB 10.6.x:
openxs@ao756:~/dbs/maria10.6$ bin/mysql --socket=/tmp/mariadb.sock
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 6
|
Server version: 10.6.5-MariaDB MariaDB Server
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
MariaDB [(none)]> select json_object('attr2', ifnull(u.o, json_array())) j from
|
mysql.user u1 left join (select user, json_arrayagg(json_object('attr1', user))
|
as o from mysql.user group by user) u on u1.user = u.user where u1.user = 'root';
|
+--------------------------------------+
|
| j |
|
+--------------------------------------+
|
| {"attr2": "[{\"attr1\": \"root\"}]"} |
|
+--------------------------------------+
|
1 row in set (0.007 sec)
|
It will return a single row for the "root" user, but note that attributes and values in the array have backslash before the ". While with MySQL:
openxs@ao756:~/dbs/maria10.6$ bin/mysql -uroot --socket=/tmp/maysql8.sock
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MySQL connection id is 11
|
Server version: 8.0.26 Source distribution
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
MySQL [(none)]> select json_object('attr2', ifnull(u.o, json_array())) j from mysql.user u1 left join (select user, json_arrayagg(json_object('attr1', user)) as o from mysql.user group by user) u on u1.user = u.user where u1.user = 'root';
|
+--------------------------------+
|
| j |
|
+--------------------------------+
|
| {"attr2": [{"attr1": "root"}]} |
|
+--------------------------------+
|
1 row in set (0.002 sec)
|
we have expected quoting for the values in the array.
Why is this difference, that I consider a bug in MariaDB?
Bug
Major
MDEV-16620 Add support for JSON_ARRAYAGG and JSON_OBJECTAGG
