MongoDB多关联文档查询异常：无交易时返回[{}]而非[]

我有products、transactions和users三个集合：

• 每个产品关联若干交易
• 每个交易关联一个用户

查询产品时需要实现：

1. 基于productId关联生成交易数组
2. 每个交易通过travellerId关联users集合中的用户信息

当前聚合查询在有交易时输出符合预期，但无交易时，transactions字段返回[{}]而非[]。

const tripProducts = await db.collection('products')
                .aggregate([
                    { $match: { tripId: new ObjectId(tripId ?? '') } },
                    { $lookup: { from: 'transactions', localField: '_id', foreignField: 'productId', as: 'transactions' } },
                    { $unwind: { path: '$transactions', preserveNullAndEmptyArrays: true } }, // Unwind transactions array
                    { $lookup: { from: 'users', localField: 'transactions.travellerId', foreignField: '_id', as: 'traveller' } },
                    { $unwind: { path: '$traveller', preserveNullAndEmptyArrays: true } }, // Unwind traveller array
                    { $addFields: { 'transactions.traveller': '$traveller' } },
                    {
                        $group: {
                            _id: '$_id',
                            // Include all fields from products using $first or similar operators
                            tripId: { $first: '$tripId' },
                            name: { $first: '$name' },
                            category: { $first: '$category' },
                            productPrice: { $first: '$productPrice' },
                            currency: { $first: '$currency' },
                            depositForBusiness: { $first: '$depositForBusiness' },
                            depositDate: { $first: '$depositDate' },
                            finalDate: { $first: '$finalDate' },
                            minTravellers: { $first: '$minTravellers' },
                            maxTravellers: { $first: '$maxTravellers' },
                            amount: { $first: '$amount' },
                            depositAmount: { $first: '$depositAmount' },
                            platformFeeForDeposit: { $first: '$platformFeeForDeposit' },
                            platformFee: { $first: '$platformFee' },
                            userId: { $first: '$userId' },
                            transactions: { $push: '$transactions' } // Push the transactions with the traveller details
                        }
                    }
                ])
                .toArray();

[
    {
        "_id": "66d62cb0caeb8c9e204a10b5",
        "tripId": "66d62bc5caeb8c9e204a10b3",
        "name": "Next Year",
        "category": "🏨 Stay",
        "productPrice": 10000,
        "currency": "usd",
        "depositForBusiness": 1000,
        "depositDate": "2024-09-04T19:00:00.000Z",
        "finalDate": "2025-04-09T19:00:00.000Z",
        "minTravellers": 1,
        "maxTravellers": 2,
        "amount": 10975.6,
        "depositAmount": 1098.1,
        "platformFeeForDeposit": 98.1,
        "platformFee": 975.6000000000004,
        "userId": "66bb9220366e6c4942f4943e",
        "transactions": [
            {}
        ]
    },
    {
        "_id": "66d73682b976159272117780",
        "tripId": "66d62bc5caeb8c9e204a10b3",
        "name": "Tomorrow",
        "category": "🏨 Stay",
        "productPrice": 1000,
        "currency": "dkk",
        "depositForBusiness": 100,
        "depositDate": "2024-09-03T19:00:00.000Z",
        "finalDate": "2024-09-03T19:00:00.000Z",
        "minTravellers": 1,
        "maxTravellers": 2,
        "amount": 1098.1,
        "depositAmount": 110.35,
        "platformFeeForDeposit": 10.35,
        "platformFee": 98.09999999999991,
        "userId": "66bb9220366e6c4942f4943e",
        "transactions": [
            {}
        ]
    }
]

问题根源 #

当产品无关联交易时：

1. 第一个$lookup返回空数组[]
2. $unwind配合preserveNullAndEmptyArrays: true会将空数组拆分为一个空对象{}
3. 后续$group的$push会把这个空对象加入数组，最终得到[{}]

优化方案（推荐） #

使用带管道的$lookup，直接在关联交易时嵌套查询用户信息，避免多次unwind和group操作，同时自然返回空数组：

const tripProducts = await db.collection('products')
                .aggregate([
                    { $match: { tripId: new ObjectId(tripId ?? '') } },
                    {
                        $lookup: {
                            from: 'transactions',
                            localField: '_id',
                            foreignField: 'productId',
                            as: 'transactions',
                            // 嵌套管道：关联交易时直接查询用户信息
                            pipeline: [
                                {
                                    $lookup: {
                                        from: 'users',
                                        localField: 'travellerId',
                                        foreignField: '_id',
                                        as: 'traveller'
                                    }
                                },
                                { $unwind: { path: '$traveller', preserveNullAndEmptyArrays: true } }
                            ]
                        }
                    }
                ])
                .toArray();

备选修复方案（针对原代码修改） #

如果不想大幅改动原代码，可在$group阶段后添加一个$addFields，判断并替换空对象数组：

// 在原聚合的$group阶段后添加
{
    $addFields: {
        transactions: {
            $cond: {
                // 判断数组第一个元素是否为空对象
                if: { $eq: [{ $size: { $objectToArray: { $first: '$transactions' } } }, 0] },
                then: [],
                else: '$transactions'
            }
        }
    }
}

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