I'm trying to compile data from 2 collections on the backend before passing to the front to render a chart. One of the collections has different property names for the dates involved and both need to compile the hours. I have an aggregate pipeline working perfect on a single collection, but I figure it should be possible to run the pipeline on both and return a single array to pass to the front. However, the response I keep getting is what I assume is an error message:

{
    "ok": 0,
    "code": 40323,
    "codeName": "Location40323",
    "$clusterTime": {
        "clusterTime": {
            "$timestamp": "7107767560271036417"
        },
        "signature": {
            "hash": "35RndgTpiJ5STtcwh8bACLC7GRM=",
            "keyId": {
                "low": 178,
                "high": 1645023840,
                "unsigned": false
            }
        }
    },
    "operationTime": {
        "$timestamp": "7107767551681101827"
    }
}

but the server stays running and no other message/error is given. I've been messing with how I approach the pipeline but get the same result. I'm still learning mongodb/mongoose (and coding in general) so I'm sure theres something I'm doing wrong, but from the different threads/articles I've read I'm not finding the issue.

There are 3 collections involved- the User, TimeBrackets, and Visits, and the latter 2 are tied to the user by a ref: on the _id. Any help would be great.

Heres the pipeline:

    let isStart = req.body.isStart
    let isEnd = req.body.isEnd
    let userId = mongoose.Types.ObjectId(req.params.id)

  User.aggregate()
    .match({ user: userId })
    .lookup({
        from: "visits",
        as: "visits",
        let: [ 
            {visitStart: 'visits.visitStart'},
            {visitEnd: 'visits.visitEnd'}

        ],
        pipeline: [
            {
                $match: {
                    $and: [
                        { user: userId },
                        {visitStart: {$gte: new Date(isStart)}},
                        {visitEnd: {$lte: new Date(isEnd)}}
                    ]
                },
                $group: {
                    _id: { $dateToString: { date: "$visitEnd", format: "%m-%d-%Y" }},
                    hours: {
                        $sum: "$totalHours"
                    }
                }
            }
        ]
    }).unwind({ path: "visits", preserveNullAndEmptyArrays: true})
    .lookup({
        from: "timebrackets",
        as: "brackets",
        let: { hours: '$timebrackets.entries.hours'},
        pipeline: [
            {
                $match: {
                    $and: [
                        {user: userId},
                        {date: {$gte: new Date(isStart)}},
                        {date: {$lte: new Date(isEnd)}}
                    ]
                },
                $group: {
                    _id: { $dateToString: { date: "$date", format: "%m-%d-%Y"}},
                    hours: {
                        $sum: "$hours"
                    }
                }
            }
        ]
    }).unwind({ path: "brackets", preserveNullAndEmptyArrays: true})
    .project({
        _id: 0,
        date: "$_id",
        brackets: 1,
        visits: 1

    })
    .sort({ date: 'asc'})
    .exec((err, result) => {
       if (err) {
           res.send(err);
       } else {
           res.json(result);
       }
    });

UPDATE so the actual error message is: A pipeline stage specification object must contain exactly one field. I've looked over the stages and unless I'm missing something, I think my syntax is correct with where the {} are placed...

CodePudding user response：

So ultimately it came down to syntax, the error: A pipeline stage specification object must contain exactly one field, seems to generally infer that somewhere the {} are off. I had kept trying to wrap the lookup sub-pipelines in {}, but was forgetting to remove the initial {} set within the pipeline array. So essentially I was setting it as :

pipeline: [
            { <-- this being the error
              {$match: ...}
              {$group: ...}