mongo db lookup with list of IDs and count a certain field and add it to the original document-CodePudding

I have two different collection. following are -

Collection A (which consist a list of pipeline ID):

    {
        "_id" : "1c0c97b6-8313-4ed2-8fd3-abbb82fe5e72",
        "minToClose" : 1,
        
        "pipelines" : ["c6ce1f81-b109-4f70-a9b7-53322c1b1a93","f9a0eb75-b56e-4819-a836-e5ae9abb43cd","b0c5d1dd-6af0-40d0-a0c9-0e659d62b0fa" ],
        "extra" : {
            "autoPair" : "2022-06-02T09:36:15.317Z"
        },
        "createdAt" : ISODate("2022-06-02T09:36:15.266Z"),
        "updatedAt" : ISODate("2022-06-02T09:36:15.266Z"),
    },{
        "_id" : "26965615-979b-49dc-99ef-47be25c2d4c2",
        "minToClose" : 3,
        "pipelines" : ["c6ce1f81-b109-4f70-a9b7-53322c1b1a93","f9a0eb75-b56e-4819-a836-e5ae9abb43cd", "50d577ec-d4bc-4417-8d9c-db1d4795d29b","264f4f2c-baff-443f-82dc-cc462aa67bd6","b0c5d1dd-6af0-40d0-a0c9-0e659d62b0fa"   ],
        "extra" : {
            "autoPair" : "2022-06-02T09:36:15.317Z"
        },
        "createdAt" : ISODate("2022-06-02T09:36:15.266Z"),
        "updatedAt" : ISODate("2022-06-02T09:36:15.266Z"),
    }

now we have pipeline collection :

    {
    "_id" : "c6ce1f81-b109-4f70-a9b7-53322c1b1a93",
    "status" : "OPEN",
    "isAbandoned" : false,
    "createdAt" : ISODate("2022-05-27T16:13:22.160Z"),
    "updatedAt" : ISODate("2022-05-27T16:13:45.779Z")
     },{
    "_id" : "f9a0eb75-b56e-4819-a836-e5ae9abb43cd",
    "status" : "CLOSE",
    "isAbandoned" : false,
    "createdAt" : ISODate("2022-05-27T16:13:22.160Z"),
    "updatedAt" : ISODate("2022-05-27T16:13:45.779Z")
     },
{
    "_id" : "b0c5d1dd-6af0-40d0-a0c9-0e659d62b0fa",
    "status" : "CLOSE",
    "isAbandoned" : false,
    "createdAt" : ISODate("2022-05-27T16:13:22.160Z"),
    "updatedAt" : ISODate("2022-05-27T16:13:45.779Z")
     },{
    "_id" : "50d577ec-d4bc-4417-8d9c-db1d4795d29b",
    "status" : "CANCEL",
    "isAbandoned" : false,
    "createdAt" : ISODate("2022-05-27T16:13:22.160Z"),
    "updatedAt" : ISODate("2022-05-27T16:13:45.779Z")
     },{
    "_id" : "264f4f2c-baff-443f-82dc-cc462aa67bd6",
    "status" : "OPEN",
    "isAbandoned" : false,
    "createdAt" : ISODate("2022-05-27T16:13:22.160Z"),
    "updatedAt" : ISODate("2022-05-27T16:13:45.779Z")
     }

I want to count the number of Close cancel and place it in the extra map of collection A . Like the below code :

{
        "_id" : "1c0c97b6-8313-4ed2-8fd3-abbb82fe5e72",
        "minToClose" : 1,
        
        "pipelines" : ["c6ce1f81-b109-4f70-a9b7-53322c1b1a93","f9a0eb75-b56e-4819-a836-e5ae9abb43cd","b0c5d1dd-6af0-40d0-a0c9-0e659d62b0fa" ],
        "extra" : {
            "autoPair" : "2022-06-02T09:36:15.317Z",
            "totalClosedandCanceledCount" :  2
        },
        "createdAt" : ISODate("2022-06-02T09:36:15.266Z"),
        "updatedAt" : ISODate("2022-06-02T09:36:15.266Z"),
    },{
        "_id" : "26965615-979b-49dc-99ef-47be25c2d4c2",
        "minToClose" : 3,
        "pipelines" : ["c6ce1f81-b109-4f70-a9b7-53322c1b1a93","f9a0eb75-b56e-4819-a836-e5ae9abb43cd", "50d577ec-d4bc-4417-8d9c-db1d4795d29b","264f4f2c-baff-443f-82dc-cc462aa67bd6","b0c5d1dd-6af0-40d0-a0c9-0e659d62b0fa"   ],
        "extra" : {
            "autoPair" : "2022-06-02T09:36:15.317Z",
            "totalClosedandCanceledCount" :  3
        },
        "createdAt" : ISODate("2022-06-02T09:36:15.266Z"),
        "updatedAt" : ISODate("2022-06-02T09:36:15.266Z"),
    }

I tried to write the mongo aggregation. but failed to Iterate the list.Any help?

CodePudding user response：

Here is an attempt at your aggregation needs...

Aggregation

db.a.aggregate([
    { $addFields: { pipelinesOriginal: "$pipelines" } }, 
    { $unwind: "$pipelines" },
    {
        $lookup:
        {
            from: "pipeline",
            localField: "pipelines",
            foreignField: "_id",
            as: "lookupValues"
        }
    },
    {
        $unwind: "$lookupValues"
    },
    {
        $match: { "lookupValues.status": { $in: [ "CLOSE", "CANCEL" ] } }
    },
    {
        $group:
        {
            _id: "$_id",
            minToClose: { $first: "$minToClose" },
            pipelines: { $first: "$pipelinesOriginal" },
            extra: { $first: "$extra" },
            createdAt: { $first: "$createdAt" },
            updatedAt: { $first: "$updatedAt" },
            count: { $sum: 1}            
        }
    },
    { $addFields: { "extra.totalClosedandCanceledCount": { "$toInt": "$count" } } },
    { $project: { "count": 0 } }
]).pretty()

Results

{
        "_id" : "26965615-979b-49dc-99ef-47be25c2d4c2",
        "minToClose" : 3,
        "pipelines" : [
                "c6ce1f81-b109-4f70-a9b7-53322c1b1a93",
                "f9a0eb75-b56e-4819-a836-e5ae9abb43cd",
                "50d577ec-d4bc-4417-8d9c-db1d4795d29b",
                "264f4f2c-baff-443f-82dc-cc462aa67bd6",
                "b0c5d1dd-6af0-40d0-a0c9-0e659d62b0fa"
        ],
        "extra" : {
                "autoPair" : "2022-06-02T09:36:15.317Z",
                "totalClosedandCanceledCount" : 3
        },
        "createdAt" : ISODate("2022-06-02T09:36:15.266Z"),
        "updatedAt" : ISODate("2022-06-02T09:36:15.266Z")
}
{
        "_id" : "1c0c97b6-8313-4ed2-8fd3-abbb82fe5e72",
        "minToClose" : 1,
        "pipelines" : [
                "c6ce1f81-b109-4f70-a9b7-53322c1b1a93",
                "f9a0eb75-b56e-4819-a836-e5ae9abb43cd",
                "b0c5d1dd-6af0-40d0-a0c9-0e659d62b0fa"
        ],
        "extra" : {
                "autoPair" : "2022-06-02T09:36:15.317Z",
                "totalClosedandCanceledCount" : 2
        },
        "createdAt" : ISODate("2022-06-02T09:36:15.266Z"),
        "updatedAt" : ISODate("2022-06-02T09:36:15.266Z")
}

Explanation

Stage 1 - { $addFields: { pipelinesOriginal: "$pipelines" } }

Need to retain the array of pipeline key values for final output. Stage 2 will modify the document values as needed for parsing...

Stage 2 - { $unwind: "$pipelines" }

The third stage needs the foreign keys on a separate record. For this reason, break them out using $unwind.

Stage 3 - { $lookup: { from: "pipeline", localField: "pipelines", foreignField: "_id", as: "lookupValues" } }

Get details from a related table/collection. This puts the found results into an array in each related document called lookupValues.

Stage 4 - { $unwind: "$lookupValues" }

For stage 6 grouping and aggregate functions we need a record per found lookup record. For this reason, unwind the found values in the array lookupValues.

Stage 5 - { $match: { "lookupValues.status": { $in: [ "CLOSE", "CANCEL" ] } } }

Only consider status values of CLOSE, or CANCEL for counting.

Stage 6 - $group (not putting the entire operator here -too long)

Basically, group by the original _id field value and count each record matched in stage 5. Use the $first operator to retain the other fields. Notice we put back the original pipeline array in its original form here.

Stage 7 - { $addFields: { "extra.totalClosedandCanceledCount": "$count" } }

Move the count to the desired area of the document. Here we create and embed a new field totalClosedandCanceledCount under the existing field extra.

Stage 8 - { $project: { "count": 0 } }

Remove the field count by using a projection. We have already copied this field to the desired location in extra.totalClosedandCanceledCount and we no longer need the field count.

Conclusion

There is no sorting predicate and no sort was specified.

Appendix

Test Data

db.a.insertMany([{
        "_id" : "1c0c97b6-8313-4ed2-8fd3-abbb82fe5e72",
        "minToClose" : 1,
        "pipelines" : [
                "c6ce1f81-b109-4f70-a9b7-53322c1b1a93",
                "f9a0eb75-b56e-4819-a836-e5ae9abb43cd",
                "b0c5d1dd-6af0-40d0-a0c9-0e659d62b0fa"
        ],
        "extra" : {
                "autoPair" : "2022-06-02T09:36:15.317Z"
        },
        "createdAt" : ISODate("2022-06-02T09:36:15.266Z"),
        "updatedAt" : ISODate("2022-06-02T09:36:15.266Z")
},
{
        "_id" : "26965615-979b-49dc-99ef-47be25c2d4c2",
        "minToClose" : 3,
        "pipelines" : [
                "c6ce1f81-b109-4f70-a9b7-53322c1b1a93",
                "f9a0eb75-b56e-4819-a836-e5ae9abb43cd",
                "50d577ec-d4bc-4417-8d9c-db1d4795d29b",
                "264f4f2c-baff-443f-82dc-cc462aa67bd6",
                "b0c5d1dd-6af0-40d0-a0c9-0e659d62b0fa"
        ],
        "extra" : {
                "autoPair" : "2022-06-02T09:36:15.317Z"
        },
        "createdAt" : ISODate("2022-06-02T09:36:15.266Z"),
        "updatedAt" : ISODate("2022-06-02T09:36:15.266Z")
}])


db.pipeline.insertMany([{
        "_id" : "c6ce1f81-b109-4f70-a9b7-53322c1b1a93",
        "status" : "OPEN",
        "isAbandoned" : false,
        "createdAt" : ISODate("2022-05-27T16:13:22.160Z"),
        "updatedAt" : ISODate("2022-05-27T16:13:45.779Z")
},
{
        "_id" : "f9a0eb75-b56e-4819-a836-e5ae9abb43cd",
        "status" : "CLOSE",
        "isAbandoned" : false,
        "createdAt" : ISODate("2022-05-27T16:13:22.160Z"),
        "updatedAt" : ISODate("2022-05-27T16:13:45.779Z")
},
{
        "_id" : "b0c5d1dd-6af0-40d0-a0c9-0e659d62b0fa",
        "status" : "CLOSE",
        "isAbandoned" : false,
        "createdAt" : ISODate("2022-05-27T16:13:22.160Z"),
        "updatedAt" : ISODate("2022-05-27T16:13:45.779Z")
},
{
        "_id" : "50d577ec-d4bc-4417-8d9c-db1d4795d29b",
        "status" : "CANCEL",
        "isAbandoned" : false,
        "createdAt" : ISODate("2022-05-27T16:13:22.160Z"),
        "updatedAt" : ISODate("2022-05-27T16:13:45.779Z")
},
{
        "_id" : "264f4f2c-baff-443f-82dc-cc462aa67bd6",
        "status" : "OPEN",
        "isAbandoned" : false,
        "createdAt" : ISODate("2022-05-27T16:13:22.160Z"),
        "updatedAt" : ISODate("2022-05-27T16:13:45.779Z")
}])

EDIT

Per requirements clarification, need to have a count of zero for records in collection 'a' where no related records are closed or cancelled.

TEST RECORDS

db.a.drop();
db.a.insertMany([{
        "_id" : "1c0c97b6-8313-4ed2-8fd3-abbb82fe5e72",
        "minToClose" : 1,
        "pipelines" : [
                "c6ce1f81-b109-4f70-a9b7-53322c1b1a93",
                "f9a0eb75-b56e-4819-a836-e5ae9abb43cd",
                "b0c5d1dd-6af0-40d0-a0c9-0e659d62b0fa"
        ],
        "extra" : {
                "autoPair" : "2022-06-02T09:36:15.317Z"
        },
        "createdAt" : ISODate("2022-06-02T09:36:15.266Z"),
        "updatedAt" : ISODate("2022-06-02T09:36:15.266Z")
},
{
        "_id" : "26965615-979b-49dc-99ef-47be25c2d4c2",
        "minToClose" : 3,
        "pipelines" : [
                "c6ce1f81-b109-4f70-a9b7-53322c1b1a93",
                "f9a0eb75-b56e-4819-a836-e5ae9abb43cd",
                "50d577ec-d4bc-4417-8d9c-db1d4795d29b",
                "264f4f2c-baff-443f-82dc-cc462aa67bd6",
                "b0c5d1dd-6af0-40d0-a0c9-0e659d62b0fa"
        ],
        "extra" : {
                "autoPair" : "2022-06-02T09:36:15.317Z"
        },
        "createdAt" : ISODate("2022-06-02T09:36:15.266Z"),
        "updatedAt" : ISODate("2022-06-02T09:36:15.266Z")
},
{
        "_id" : "d5c54949-93b1-4a38-a2ed-067803448b8d",
        "minToClose" : 1,
        "pipelines" : [
                "b1cad01b-b1c7-46d5-8eef-00fe4c3eb68b",
                "a22d0ba6-8494-437d-ac4e-d470be850684",
                "bf10be9d-3214-4c64-9bc9-5e9d4beea746"
        ],
        "extra" : {
                "autoPair" : "2022-06-01T09:36:15.317Z"
        },
        "createdAt" : ISODate("2022-06-01T09:36:15.266Z"),
        "updatedAt" : ISODate("2022-06-01T09:36:15.266Z")
}])








db.pipeline.drop()
db.pipeline.insertMany([{
        "_id" : "c6ce1f81-b109-4f70-a9b7-53322c1b1a93",
        "status" : "OPEN",
        "isAbandoned" : false,
        "createdAt" : ISODate("2022-05-27T16:13:22.160Z"),
        "updatedAt" : ISODate("2022-05-27T16:13:45.779Z")
},
{
        "_id" : "f9a0eb75-b56e-4819-a836-e5ae9abb43cd",
        "status" : "CLOSE",
        "isAbandoned" : false,
        "createdAt" : ISODate("2022-05-27T16:13:22.160Z"),
        "updatedAt" : ISODate("2022-05-27T16:13:45.779Z")
},
{
        "_id" : "b0c5d1dd-6af0-40d0-a0c9-0e659d62b0fa",
        "status" : "CLOSE",
        "isAbandoned" : false,
        "createdAt" : ISODate("2022-05-27T16:13:22.160Z"),
        "updatedAt" : ISODate("2022-05-27T16:13:45.779Z")
},
{
        "_id" : "50d577ec-d4bc-4417-8d9c-db1d4795d29b",
        "status" : "CANCEL",
        "isAbandoned" : false,
        "createdAt" : ISODate("2022-05-27T16:13:22.160Z"),
        "updatedAt" : ISODate("2022-05-27T16:13:45.779Z")
},
{
        "_id" : "264f4f2c-baff-443f-82dc-cc462aa67bd6",
        "status" : "OPEN",
        "isAbandoned" : false,
        "createdAt" : ISODate("2022-05-27T16:13:22.160Z"),
        "updatedAt" : ISODate("2022-05-27T16:13:45.779Z")
},
{
        "_id" : "b1cad01b-b1c7-46d5-8eef-00fe4c3eb68b",
        "status" : "OPEN",
        "isAbandoned" : false,
        "createdAt" : ISODate("2022-05-27T16:13:22.160Z"),
        "updatedAt" : ISODate("2022-05-27T16:13:45.779Z")
},
{
        "_id" : "a22d0ba6-8494-437d-ac4e-d470be850684",
        "status" : "OPEN",
        "isAbandoned" : false,
        "createdAt" : ISODate("2022-05-27T16:13:22.160Z"),
        "updatedAt" : ISODate("2022-05-27T16:13:45.779Z")
},
{
        "_id" : "bf10be9d-3214-4c64-9bc9-5e9d4beea746",
        "status" : "OPEN",
        "isAbandoned" : false,
        "createdAt" : ISODate("2022-05-27T16:13:22.160Z"),
        "updatedAt" : ISODate("2022-05-27T16:13:45.779Z")
}])

Aggregation

db.a.aggregate([
    { $addFields: { pipelinesOriginal: "$pipelines" } }, 
    { $unwind: "$pipelines" },
    {
        $lookup:
        {
            from: "pipeline",
            localField: "pipelines",
            foreignField: "_id",
            as: "lookupValues"
        }
    },
    { $unwind: "$lookupValues" },
    {
        $project:
        {
            _id: 1,
            minToClose: 1,
            pipelines: "$pipelinesOriginal",
            extra: 1,
            createdAt: 1,
            updatedAt: 1,
            lookupValues: 1,
            closedFlag:
            {
                $switch:
                {
                    branches:
                    [
                        { "case": { $eq: [ "$lookupValues.status", "CLOSE" ] }, then: 1 },
                        { "case": { $eq: [ "$lookupValues.status", "CANCEL" ] }, then: 1 }
                    ],
                    default: 0
                }
            }
        }
    },
    {
        $group:
        {
            _id: "$_id",
            minToClose: { $first: "$minToClose" },
            pipelines: { $first: "$pipelines" },
            extra: { $first: "$extra" },
            createdAt: { $first: "$createdAt" },
            updatedAt: { $first: "$updatedAt" },
            count: { $sum: "$closedFlag"} 
        }
    },
    { $addFields: { "extra.totalClosedandCanceledCount": { "$toInt": "$count" } } },
    { $project: { "count": 0 } }
]).pretty()

Results

{
        "_id" : "d5c54949-93b1-4a38-a2ed-067803448b8d",
        "minToClose" : 1,
        "pipelines" : [
                "b1cad01b-b1c7-46d5-8eef-00fe4c3eb68b",
                "a22d0ba6-8494-437d-ac4e-d470be850684",
                "bf10be9d-3214-4c64-9bc9-5e9d4beea746"
        ],
        "extra" : {
                "autoPair" : "2022-06-01T09:36:15.317Z",
                "totalClosedandCanceledCount" : 0
        },
        "createdAt" : ISODate("2022-06-01T09:36:15.266Z"),
        "updatedAt" : ISODate("2022-06-01T09:36:15.266Z")
}
{
        "_id" : "26965615-979b-49dc-99ef-47be25c2d4c2",
        "minToClose" : 3,
        "pipelines" : [
                "c6ce1f81-b109-4f70-a9b7-53322c1b1a93",
                "f9a0eb75-b56e-4819-a836-e5ae9abb43cd",
                "50d577ec-d4bc-4417-8d9c-db1d4795d29b",
                "264f4f2c-baff-443f-82dc-cc462aa67bd6",
                "b0c5d1dd-6af0-40d0-a0c9-0e659d62b0fa"
        ],
        "extra" : {
                "autoPair" : "2022-06-02T09:36:15.317Z",
                "totalClosedandCanceledCount" : 3
        },
        "createdAt" : ISODate("2022-06-02T09:36:15.266Z"),
        "updatedAt" : ISODate("2022-06-02T09:36:15.266Z")
}
{
        "_id" : "1c0c97b6-8313-4ed2-8fd3-abbb82fe5e72",
        "minToClose" : 1,
        "pipelines" : [
                "c6ce1f81-b109-4f70-a9b7-53322c1b1a93",
                "f9a0eb75-b56e-4819-a836-e5ae9abb43cd",
                "b0c5d1dd-6af0-40d0-a0c9-0e659d62b0fa"
        ],
        "extra" : {
                "autoPair" : "2022-06-02T09:36:15.317Z",
                "totalClosedandCanceledCount" : 2
        },
        "createdAt" : ISODate("2022-06-02T09:36:15.266Z"),
        "updatedAt" : ISODate("2022-06-02T09:36:15.266Z")
}