如何编写Couchbase查询实现文档数组对象扁平化？

问题背景 #

现有Couchbase文档结构如下：

{
      "appId": "f1606a90-c96f-4db9-8e5f-9f0b6d2d2106",
      "attributes": [
         {
            "label": "Reviewed By",
            "name": "reviewedBy",
            "value": "QA.Praveen"
         },
         {
            "label": "Assigned To",
            "name": "assignedTo",
            "value": "Praveen"
         },
         {
            "label": "Completed Lines",
            "name": "completedLines",
            "value": "23"
         }
      ],
    "hasConversations": false,
    "id": "02efdb67-798f-4616-9750-c80c17c4c1ee",
    "status": "Completed",
    "task": {
        "details": {
            "hasAttachments": true,
            "importance": "normal",
            "subject": "very interesting"
        },
        "taskType": "mail"
    },
    "taskId": "02efdb67-798f-4616-9750-c80c17c4c1ee",
    "type": "task"
}

需求是编写N1QL查询，将文档中的嵌套数组attributes和嵌套对象task扁平化，生成完全扁平的文档结构。尝试过以下查询但未达到预期：

SELECT a.taskId, a.appId, ARRAY_FLATTEN( ARRAY_AGG ({ attr.name:attr.`value`}) ,0) as attr FROM workFlowDetails a UNNEST a.attributes attr WHERE a.type = "task" and a.taskId = '02efdb67-798f-4616-9750-c80c17c4c1ee' GROUP BY  a.taskId, a.appId;

期望输出结构：

{
    "appId": "f1606a90-c96f-4db9-8e5f-9f0b6d2d2106",
    "taskId": "02efdb67-798f-4616-9750-c80c17c4c1ee",
    "type": "task",
    "hasConversations": false,
    "id": "02efdb67-798f-4616-9750-c80c17c4c1ee",
    "status": "Completed",
    "reviewedBy": "QA.Praveen",
    "assignedTo": "Praveen",
    "completedLines": "23",
    "taskType": "mail",
    "hasAttachments": true,
    "importance": "normal",
    "subject": "very interesting"
}

正确的N1QL查询语句 #

SELECT 
    a.* UNSET a.attributes, a.task,
    OBJECT_AGG(attr.name, attr.`value`) AS *,
    a.task.taskType,
    a.task.details.*
FROM workFlowDetails a
UNNEST a.attributes attr
WHERE a.type = "task" AND a.taskId = '02efdb67-798f-4616-9750-c80c17c4c1ee'
GROUP BY a;

语句说明 #

• a.* UNSET a.attributes, a.task：保留文档原始字段，同时移除不需要的嵌套字段attributes和task
• OBJECT_AGG(attr.name, attr.value) AS *：通过聚合函数将UNNEST后的attributes数组元素转换为扁平键值对，并合并到结果中
• a.task.taskType, a.task.details.*：直接提取task对象中的顶层字段和details子对象的所有字段，拉平到结果顶层
• GROUP BY a：确保每个原始文档仅生成一条结果，避免UNNEST后产生多条数据

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