将列转换为 JSON 数组的最佳方法(甲骨文)

Question

将列的数据合并到 json 数组的最佳方法是什么？

我有桌子

Foreign_ID | Primary_ID | Position | Name
143         2344        Postman     Ann
143         2345        Postman     Tim
144         2346        Postman     Bob
144         2347        Postman     Dan
143         2348        Student     Jim
143         2349        Student     Corey
146         2350        Student     Justin
146         2351        Student     Keith

我需要为每个外国 ID 将名称列中的数据合并到 json 数组，其中行在位置列中具有相同的数据。然后，此json数组应插入到值合并的行之一的名称列中。结果表应如下所示：

Foreign_ID | Primary_ID | Position  | Name
143         2344       Postman     ["Ann","Tim"]
144         2346       Postman     ["Bob","Dan"]
143         2348       Student     ["Jim","Corey"]
146         2350       Student     ["Justin","Keith"]

谢谢。

Answer 1

看起来我已经找到了解决方案，查询是

MERGE INTO TESTSCHEDULER TS
USING (SELECT Foreign_ID,
Position,
MIN(Primary_ID) AS MINID,
COUNT(*),
JSON_ARRAYAGG(Name) AS JSONNAME
FROM TESTSCHEDULER
GROUP BY Position,
Foreign_ID
HAVING COUNT(*) > 1
) JSONTABLE ON (TS.Position = JSONTABLE.Position AND TS.Foreign_ID = JSONTABLE.Foreign_ID)
WHEN MATCHED THEN
UPDATE SET TS.NAME = JSONTABLE.JSONNAME WHERE TS.Primary_ID = JSONTABLE.MINID;
DELETE FROM TESTSCHEDULER WHERE Primary_ID IN (   
SELECT Primary_ID FROM TESTSCHEDULER
INNER JOIN  (SELECT Foreign_ID,
Position,
MIN(Primary_ID) AS MINID,
COUNT(*)
FROM TESTSCHEDULER
GROUP BY Position,  Foreign_ID
HAVING COUNT(*) > 1) JSONTABLE
ON TESTSCHEDULER.FOREIGN_ID = JSONTABLE.Foreign_ID 
AND TESTSCHEDULER.Position = JSONTABLE.Position 
AND TESTSCHEDULER.PRIMARY_ID > JSONTABLE.MINID);

其中JSON_ARRAYAGG(col)函数适用于数据库版本 12+ ...对于低于 12.2 的版本，替代JSON_ARRAYAGG为：

'[' || replace(rtrim (xmlagg (xmlelement (e, '"' || Name || '",')).extract ('//text()'), ','), '"','"') || ']' AS JSONNAME

这是用于clob列，对于varchar使用LISTAGG