如何通过NetSuite SuiteQL实现关联工单的嵌套结构查询结果？

问题说明 #

原本通过SuiteQL查询联系人及关联客户信息时结果正常：

SELECT TOP 5 co.company, cu.companyname, co.email, co.id, co.entitytitle 
FROM contact AS co 
LEFT JOIN customer as cu on cu.id = co.company

但关联supportcase表后，同一联系人会因对应多条工单返回重复行：

SELECT TOP 5 s.id as t, co.company, cu.companyname, co.email, co.id, co.entitytitle 
FROM contact AS co 
LEFT JOIN customer as cu on cu.id = co.company 
LEFT JOIN supportcase as s ON s.company = co.company

需要实现每个联系人对应嵌套工单列表的结构，而非重复行。

可行方案 #

方案1：使用聚合函数拼接工单信息（一次查询） #

利用NetSuite SuiteQL支持的LISTAGG函数，将同一联系人的所有工单信息拼接为结构化字符串，客户端拿到后可解析为嵌套列表：

SELECT TOP 5 
  co.company, 
  cu.companyname, 
  co.email, 
  co.id, 
  co.entitytitle,
  LISTAGG(JSON_OBJECT('case_id', s.id, 'case_title', s.title), ';') WITHIN GROUP (ORDER BY s.id) AS linked_cases
FROM contact AS co 
LEFT JOIN customer as cu on cu.id = co.company 
LEFT JOIN supportcase as s ON s.company = co.company
GROUP BY co.company, cu.companyname, co.email, co.id, co.entitytitle

• 注意：LISTAGG在NetSuite 2021.1及以后版本可用，可根据需求调整拼接的工单字段，客户端只需按分隔符拆分后解析JSON即可得到嵌套的工单列表。

方案2：通过REST API关联资源分步查询 #

先通过SuiteQL获取联系人基础数据，再针对每个联系人调用NetSuite REST Record API的关联资源端点获取工单：

1. 第一步：执行原联系人查询，拿到所有目标联系人的ID；
2. 第二步：对每个联系人ID，调用/services/rest/record/v1/contact/{contactId}/supportcase接口，直接获取该联系人关联的工单列表。

• 优势：返回原生的嵌套结构，可直接获取工单的完整字段信息；
• 劣势：需发起多次请求，适合数据量较小或需要完整工单详情的场景。

内容的提问来源于stack exchange，提问作者Webdeveloper_Jelle