Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5.1, 10.5
-
None
-
Win 10
Description
When JSON_ARRAYAGG is used in a view, querying that view results in missing [ and ] brackets.
create or replace view dummy as ( |
|
|
select json_arrayagg(json_object( |
"type", "permPeriod", |
"id", ID |
)) as JSON_DATA |
|
from TEST |
|
|
group by COL1 |
)
|
|
|
select * from dummy |
gives:
{"type": "permPeriod", "id": 7},{"type": "permPeriod", "id": 8}
|
instead of:
[{"type": "permPeriod", "id": 7},{"type": "permPeriod", "id": 8}]
|
Attachments
Issue Links
- relates to
-
MDEV-16620 Add support for JSON_ARRAYAGG and JSON_OBJECTAGG
-
- Closed
-
-
-
- Closed
-
Activity
I might be mistaken, but after upgrading my MariaDB to 10.5.8, this issue is back again?
I am not using it in a view, but just a regular select:
SELECT
trid,
JSON_ARRAYAGG(
JSON_OBJECT(
'teid', teid,
'tename', tename,
'trid', trid,
'trname', trname,
'medium', temedium,
'contacttype', te.tecontacttype,
'stoptype', te.testop,
'contactid', te.tecontactid,
'templateid', tt.ttid,
'templatename', tt.ttname,
'templatesubject', tt.ttsubject,
'templatetext', tt.tttext
)
) as trevents
FROM trackingrules tr
RIGHT JOIN trackingevents te ON te.tetrid = tr.trid AND te.teactive = 1 AND te.temedium > 0
LEFT JOIN trackingtemplates tt ON tt.ttid = te.tetemplateid
WHERE tr.trisactive = 1
GROUP BY trid
Thanks for the report!
Reproducible on 10.5
MariaDB [test]> show create view v1;+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+| View | Create View | character_set_client | collation_connection |+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+| v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select group_concat(json_object('type','permPeriod','id',`t1`.`id`) separator ',') AS `JSON_DATA` from `t1` group by `t1`.`i`) | utf8 | utf8_general_ci |+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+1 row in set (0.001 sec)MariaDB [test]> select * from v1 ;+-----------------------------------------------------------------+| JSON_DATA |+-----------------------------------------------------------------+| {"type": "permPeriod", "id": 7},{"type": "permPeriod", "id": 8} |+-----------------------------------------------------------------+1 row in set (0.002 sec)MariaDB [test]> SELECT json_arrayagg(json_object( "type", "permPeriod", "id", ID )) AS JSON_DATA-> FROM t1-> GROUP BY i ;+-------------------------------------------------------------------+| JSON_DATA |+-------------------------------------------------------------------+| [{"type": "permPeriod", "id": 7},{"type": "permPeriod", "id": 8}] |+-------------------------------------------------------------------+1 row in set (0.004 sec)