Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5
-
None
Description
I'm looking at how JSON_ARRAYAGG and JSON_OBJECTAGG work at processing JSON
documents.
I'm trying these examples:
create table t30 (col1 json); |
insert into t30 values('{"color":"red", "size":1}' ); |
insert into t30 values('{"color":"blue", "size":2}' ); |
MariaDB [test]> select JSON_ARRAYAGG(col1) from t30;
|
+------------------------------------------------------------------------+
|
| JSON_ARRAYAGG(col1) |
|
+------------------------------------------------------------------------+
|
| ["{\"color\":\"red\", \"size\":1}","{\"color\":\"blue\", \"size\":2}"] |
|
+------------------------------------------------------------------------+
|
while on MySQL-8, one gets:
select JSON_ARRAYAGG(col1) from t30;
|
+-------------------------------------------------------------+
|
| JSON_ARRAYAGG(col1) |
|
+-------------------------------------------------------------+
|
| [{"size": 1, "color": "red"}, {"size": 2, "color": "blue"}] |
|
+-------------------------------------------------------------+
|
The same with JSON_OBJECTAGG:
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}' ); |
MariaDB [test]> select JSON_OBJECTAGG(name, contents) from t31;
|
+---------------------------------------------------------------------------------------+
|
| JSON_OBJECTAGG(name, contents) |
|
+---------------------------------------------------------------------------------------+
|
| {"obj1":"{\"color\":\"blue\", \"size\":2}", "obj2":"{\"color\":\"red\", \"size\":1}"} |
|
+---------------------------------------------------------------------------------------+
|
While on MySQL-8 it is:
select JSON_OBJECTAGG(name, contents) from t31;
|
+-----------------------------------------------------------------------------+
|
| JSON_OBJECTAGG(name, contents) |
|
+-----------------------------------------------------------------------------+
|
| {"obj1": {"size": 2, "color": "blue"}, "obj2": {"size": 1, "color": "red"}} |
|
+-----------------------------------------------------------------------------+
|
Attachments
Issue Links
- relates to
-
MDEV-16620 Add support for JSON_ARRAYAGG and JSON_OBJECTAGG
-
- Closed
-
According to holyfoot:
There is a way to specify explicitly the argument is JSON, so kind of workaround:
MariaDB [test]> select JSON_ARRAYAGG(json_compact(col1)) from t30;+------------------------------------------------------+| JSON_ARRAYAGG(json_compact(col1)) |+------------------------------------------------------+| [{"color":"red","size":1},{"color":"blue","size":2}] |+------------------------------------------------------+1 row in set (0.00 sec)but it should work with JSON columns, too.