Eğer json alanlar json şekilde seçildiyse bu kod işinize yarayabilir.

SELECT user_roles.id, user_roles.name, CASE WHEN json_permissions.permission_id IS NULL OR user_permissions.name IS NULL THEN NULL ELSE COALESCE( JSON_ARRAYAGG( JSON_OBJECT( 'id', json_permissions.permission_id, 'name', user_permissions.name ) ), JSON_ARRAY() ) END AS permissions FROM user_roles LEFT JOIN JSON_TABLE(user_roles.permissions, '$[*]' COLUMNS( permission_id INT PATH '$.id') ) AS json_permissions ON json_permissions.permission_id IS NOT NULL LEFT JOIN user_permissions ON json_permissions.permission_id = user_permissions.id GROUP BY user_roles.id, user_roles.name;

*Benzer bir işlemi kendi projemde bu şekilde yapmıştım.