maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hi Alexey,

I'm looking at how JSON_ARRAYAGG and JSON_OBJECTAGG work at processing JSON
documents.

I'm trying these examples, first on MySQL-8:

create table t30 (col1 json);
insert into t30 values('{"color":"red", "size":1}' );
insert into t30 values('{"color":"blue", "size":2}' );

select JSON_ARRAYAGG(col1) from t30;
+-------------------------------------------------------------+
| JSON_ARRAYAGG(col1)                                         |
+-------------------------------------------------------------+
| [{"size": 1, "color": "red"}, {"size": 2, "color": "blue"}] |
+-------------------------------------------------------------+

Good.

create table t31 (name varchar(100), contents json);
insert into t31 values('obj1', '{"color":"blue", "size":2}' );
insert into t31 values('obj2', '{"color":"red", "size":1}' );
select JSON_OBJECTAGG(name, contents) from t31;
+-----------------------------------------------------------------------------+
| JSON_OBJECTAGG(name, contents)                                              |
+-----------------------------------------------------------------------------+
| {"obj1": {"size": 2, "color": "blue"}, "obj2": {"size": 1, "color": "red"}} |
+-----------------------------------------------------------------------------+

Good.

Now, trying these on MariaDB, I get:

MariaDB [test]> select JSON_ARRAYAGG(col1) from t30;
+------------------------------------------------------------------------+
| JSON_ARRAYAGG(col1)                                                    |
+------------------------------------------------------------------------+
| ["{\"color\":\"red\", \"size\":1}","{\"color\":\"blue\", \"size\":2}"] |
+------------------------------------------------------------------------+

MariaDB [test]> select JSON_OBJECTAGG(name, contents) from t31;
+---------------------------------------------------------------------------------------+
| JSON_OBJECTAGG(name, contents)                                                        |
+---------------------------------------------------------------------------------------+
| {"obj1":"{\"color\":\"blue\", \"size\":2}", "obj2":"{\"color\":\"red\", \"size\":1}"} |
+---------------------------------------------------------------------------------------+

The JSON documents are coverted into strings.
I think, the users will prefer MySQL's behavior.

But which behaivor is "correct"? (i.e. required by the standard, etc)

Is MariaDBs's behaivor a bug? Is this expected?

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog