How to return vertices from Arango DB having one to many relationship in format like object and list of object for child vertices?

Question

I have vertices as Department and User in Arango DB. Department is having many users. Department is the parent of User. Department is having an edge with each user who belongs to a this department. I want to fetch Department and its users in format as below:

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

I'm using below query to fetch all departments with its all users.

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}

The above query returns response like:

[
    {
        "department": {
            "id": 1011,
            "createdOn": 1682680058203,
            "graphLabel": "Department",
            "name": "Boston Department",
            "updatedOn": 1682680058203
        },
        "user": {
            "emailId": "[email protected]",
            "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": "[email protected]",
            "firstName": "kevin",
            "graphLabel": "User",
            "isActive": true,
            "isVerified": true,
            "lastName": "test",
            "status": "ENABLED",
            "updatedOn": 1682680059753
        }
    }
]

Answer 1

I was able to find the solution by using LET clause.

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

The LET clause assigns the result of the subquery to the variable user. The final result is returned as an object containing the department and an array of associated users.

The query will return an array of objects, each containing the department and its associated users.