使用JSON_ARRAYAGG和JSON_OBJECT：

SELECT JSON_ARRAYAGG(
         JSON_OBJECT(
           KEY 'id'     VALUE t1.id,
           KEY 'name'   VALUE t1.name,
           KEY 'url'    VALUE t1.url,
           KEY 'target' VALUE t2.target ABSENT ON NULL
         )
       ) AS data
FROM   table_1 t1
       LEFT OUTER JOIN (
         SELECT target_id,
                JSON_ARRAYAGG(
                  JSON_OBJECT(
                    KEY 'id1' VALUE id1,
                    KEY 'col1' VALUE col1,
                    KEY 'col2' VALUE col2
                  )
                ) AS target
         FROM   table_2
         GROUP BY target_id
       ) t2
       ON t1.target_id = t2.target_id

字符串
其中，对于样本数据：

CREATE  TABLE table_1 (ID, target_id, name, url) AS
SELECT 1, 'A', 'John', 'example.org' FROM DUAL UNION ALL
SELECT 2, 'B', 'Jane', 'helloworld.com' FROM DUAL;

CREATE  TABLE table_2 (id1, target_id, col1, col2) AS
SELECT 'X', 'A', 'aaa', 'bbb' FROM DUAL UNION ALL
SELECT 'Y', 'A', 'ccc', 'ddd' FROM DUAL;

型
输出：
| DATA |
| ------------ |
| [{"id":1,"name":"John","url":"example.org ","target":[{"id1":"X","col1":"aaa","col2":"bbb"},{"id1":"Y","col1":"ccc","col2":"ddd"}]},{"id":2,"name":"Jane","url":"helloworld.com "}] |
fiddle

table1：table2 = 1：N？

SELECT AA.*, BB.* FROM table_1 AA
LEFT JOIN table_2 BB ON AA.target_id=BB.target_id
ORDER BY AA.id

字符串