|
MariaDB [test]> select json_object("a",dt.x) from (select json_arrayagg(json_object('a1', 'name')) as x) dt ;
|
+-------------------------------+
|
| json_object("a",dt.x) |
|
+-------------------------------+
|
| {"a": "[{\"a1\": \"name\"}]"} |
|
+-------------------------------+
|
1 row in set (0.006 sec)
|
|
MariaDB [test]> select json_object("a",json_arrayagg(json_object('a1', 'name'))) ;
|
+-----------------------------------------------------------+
|
| json_object("a",json_arrayagg(json_object('a1', 'name'))) |
|
+-----------------------------------------------------------+
|
| {"a": [{"a1": "name"}]} |
|
+-----------------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
|
I guess this is the same as MDEV-13701.
test from comments there:
MariaDB [test]> SELECT JSON_OBJECT("main", JSON_ARRAY("one", 2));
|
+-------------------------------------------+
|
| JSON_OBJECT("main", JSON_ARRAY("one", 2)) |
|
+-------------------------------------------+
|
| {"main": ["one", 2]} |
|
+-------------------------------------------+
|
1 row in set (0.001 sec)
|
|
MariaDB [test]> SET @sub = JSON_ARRAY("one", 2);
|
Query OK, 0 rows affected (0.001 sec)
|
|
MariaDB [test]> SELECT JSON_OBJECT("main", @sub);
|
+---------------------------+
|
| JSON_OBJECT("main", @sub) |
|
+---------------------------+
|
| {"main": "[\"one\", 2]"} |
|
+---------------------------+
|
1 row in set (0.001 sec)
|
|
|
There is a workaround for most of these cases, which is to do a JSON_MERGE() with the offending JSON value that gets treated like a string. So
MariaDB> SELECT JSON_OBJECT('foo', (SELECT JSON_ARRAYAGG(JSON_OBJECT('bar', 1))));
|
+-------------------------------------------------------------------+
|
| JSON_OBJECT('foo', (SELECT JSON_ARRAYAGG(JSON_OBJECT('bar', 1)))) |
|
+-------------------------------------------------------------------+
|
| {"foo": "[{\"bar\": 1}]"} |
|
+-------------------------------------------------------------------+
|
Can be fixed like this:
MariaDB> SELECT JSON_OBJECT('foo', JSON_MERGE('[]', (SELECT JSON_ARRAYAGG(JSON_OBJECT('bar', 1)))));
|
+-------------------------------------------------------------------------------------+
|
| JSON_OBJECT('foo', JSON_MERGE('[]', (SELECT JSON_ARRAYAGG(JSON_OBJECT('bar', 1))))) |
|
+-------------------------------------------------------------------------------------+
|
| {"foo": [{"bar": 1}]} |
|
+-------------------------------------------------------------------------------------+
|
|
|
An interesting fact is that JSON_ARRAYAGG affects JSON_TYPE
CREATE TEMPORARY TABLE temp (id LONG, msg VARCHAR(5) );
|
|
INSERT INTO temp VALUES
|
(1, "abcde"),
|
(2, "bcdea"),
|
(3, "cdeab"),
|
(4, "deabc"),
|
(5, "eabcd");
|
WITH q1 AS (
|
SELECT
|
id,
|
JSON_ARRAY(msg) AS q1a,
|
JSON_ARRAYAGG(msg) AS q1b,
|
JSON_TYPE(JSON_ARRAYAGG(msg)) AS q1c,
|
JSON_TYPE(JSON_ARRAY(msg)) AS q1d
|
FROM temp
|
)
|
SELECT
|
q1c AS s1c,
|
JSON_OBJECTAGG(id, q1a) AS s1a,
|
q1d AS s1d,
|
JSON_OBJECTAGG(id, q1b) AS s1b
|
FROM q1 \G
|
|
WITH q2 AS (
|
SELECT
|
id,
|
JSON_ARRAY(msg) AS q2a,
|
JSON_TYPE(JSON_ARRAY(msg)) AS q2b,
|
5 AS q2c,
|
JSON_TYPE(5) AS q2d
|
FROM temp
|
)
|
SELECT
|
q2b AS s2b,
|
JSON_OBJECTAGG(id, q2a) AS s2a,
|
JSON_TYPE(q2c) AS s2c,
|
q2d AS s2d
|
FROM q2 \G
|
|
WITH q3 AS (
|
SELECT
|
id,
|
JSON_ARRAYAGG(msg) AS q3a,
|
JSON_TYPE(JSON_ARRAYAGG(msg)) AS q3b,
|
5 AS q3c,
|
JSON_TYPE(5) AS q3d
|
FROM temp
|
)
|
SELECT
|
q3b AS s3b,
|
JSON_OBJECTAGG(id, JSON_MERGE('[]',q3a)) AS s3a,
|
JSON_TYPE(q3c) AS s3c,
|
q3d AS s3d
|
FROM q3 \G
|
*************************** 1. row ***************************
|
s1c: ARRA
|
s1a: {"1":"[\"abcde\"]"}
|
s1d: ARRA
|
s1b: {"1":"[\"abcde\",\"bcdea\",\"cdeab\",\"deabc\",\"eabcd\"]"}
|
*************************** 1. row ***************************
|
s2b: ARRAY
|
s2a: {"1":["abcde"], "2":["bcdea"], "3":["cdeab"], "4":["deabc"], "5":["eabcd"]}
|
s2c: INTEGER
|
s2d: INTEGER
|
*************************** 1. row ***************************
|
s3b: ARRA
|
s3a: {"1":["abcde", "bcdea", "cdeab", "deabc", "eabcd"]}
|
s3c: INTEGER
|
s3d: INTE
|
Could it be that JSON_OBJECTAGG is not detecting it as an ARRAY?
|