How to return JSON_ARRAY using an GROUP_CONCAT
Extra CASTing AS JSON for the third argument of JSON_REPLACE fixes the issue :
SELECT JSON_REPLACE(( SELECT JSON_OBJECT( 'a', 'a', 'b', 'b', 'id', null )), '$.id', CAST( CONCAT('[', GROUP_CONCAT( JSON_OBJECT('id', '123')), ']') AS JSON )) as "Result JSON"| Result : |
|---|
{ "a": "a", "b": "b", "id": [ { "id": "123" } ]}
this is what you need JSON_ARRAYAGG
SELECT JSON_REPLACE((SELECT JSON_OBJECT( 'a', 'a', 'b', 'b', 'id', null )), '$.id', (SELECT JSON_ARRAYAGG(JSON_OBJECT('id','123'))) )if not using JSON_ARRAYAGG
SELECT JSON_REPLACE((SELECT JSON_OBJECT( 'a', 'a', 'b', 'b', 'id', null )) , '$.id' , (SELECT JSON_ARRAY(CAST(group_concat(JSON_OBJECT('id','123')) as json))) )