如何从Arango DB查询一对多关系顶点，返回父对象+子列表格式

问题背景 #

ArangoDB中存在Department和User两类顶点，二者为一对多关系：Department作为父顶点，每个隶属于该部门的User顶点都与Department存在边关联。需要获取单个部门对象+下属用户列表的聚合格式数据。

期望输出格式 #

[
    {
        "department": {
            "id": 1011,
            "createdOn": 1682680058203,
            "graphLabel": "Department",
            "name": "Boston Department",
            "updatedOn": 1682680058203
        },
        "user": [
            {
                "emailId": "test@test.com",
                "firstName": "sam",
                "graphLabel": "User",
                "lastName": "test",
                "status": "ENABLED",
                "isActive": true,
                "isVerified": true,
                "createdOn": 1683875733291,
                "updatedOn": 1683875733291
            },
            {
                "emailId": "test@test2.com",
                "firstName": "kevin",
                "graphLabel": "User",
                "isActive": true,
                "isVerified": true,
                "lastName": "test",
                "status": "ENABLED",
                "updatedOn": 1682680059753
            }
        ]
    }
]

当前使用的AQL查询 #

for department in collection1 filter department.graphLabel=="Department"
for user in 1..2 any department graph 'collection1-graph' filter user.graphLabel=="User"
return {department,user}

当前查询的问题 #

返回结果中每个用户对应一条重复的部门记录，格式如下：

[
    {
        "department": {
            "id": 1011,
            "createdOn": 1682680058203,
            "graphLabel": "Department",
            "name": "Boston Department",
            "updatedOn": 1682680058203
        },
        "user": {
            "emailId": "test@test.com",
            "firstName": "sam",
            "graphLabel": "User",
            "lastName": "test",
            "status": "ENABLED",
            "isActive": true,
            "isVerified": true,
            "createdOn": 1683875733291,
            "updatedOn": 1683875733291
        }
    },
    {
        "department": {
            "id": 1011,
            "createdOn": 1682680058203,
            "graphLabel": "Department",
            "name": "Boston Department",
            "updatedOn": 1682680058203
        },
        "user": {
            "emailId": "test@test2.com",
            "firstName": "kevin",
            "graphLabel": "User",
            "isActive": true,
            "isVerified": true,
            "lastName": "test",
            "status": "ENABLED",
            "updatedOn": 1682680059753
        }
    }
]

解决方案：调整后的AQL查询 #

使用子查询+列表返回的方式，将每个部门的用户聚合为列表：

FOR department IN collection1
    FILTER department.graphLabel == "Department"
    LET users = (
        FOR user IN 1..2 ANY department GRAPH 'collection1-graph'
            FILTER user.graphLabel == "User"
            RETURN user
    )
    RETURN {
        department: department,
        user: users
    }

说明 #

• 通过LET定义子查询，一次性获取当前部门下的所有用户并返回为列表
• 最终直接返回部门对象+用户列表的结构，彻底避免重复的部门记录
• 若部门无关联用户，user字段会返回空数组[]

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