Mongo DB compare two arrays and add missing element

Question

I have a statuses array as given below.

"statuses" : [
        {
            "name" : "In Progress",
            "created_on" : ISODate("2020-04-20T19:00:07.681Z")
        },
        {
            "name" : "Pending",
            "created_on" : ISODate("2020-04-20T19:00:07.886Z")
        },
        {
            "name" : "Viewed",
            "created_on" : ISODate("2020-04-20T20:10:04.733Z")
        },
        {
            "name" : "Initial Viewed",
            "created_on" : ISODate("2020-04-20T20:10:08.468Z")
        },
        {
            "name" : "Opened",
            "created_on" : ISODate("2020-04-21T01:37:08.582Z")
        },
        {
            "name" : "Completed",
            "created_on" : ISODate("2020-04-21T01:48:46.007Z")
        }
    ]

I have another array with which:

"reference": ['In Progress', 'Pending', 'Sent', 'Initial Viewed', 'Viewed', 'Opened', 'Completed']

As it can be seen when I compare my first array with reference array, 'Sent' is missing in first array which I would like to add in my first array with current ISODate(). And also I want my first array to be in the same order as the second one.

mickl · Accepted Answer · 2020-04-24 15:31:50Z

By running $map on reference you will get desired order. $filter along with $arrayElemAt will let you find single matching element and $ifNull can be used to build new object when there's no match:

db.collection.aggregate([
    {
        $project: {
            result: {
                $map: {
                    input: "$reference",
                    as: "ref",
                    in: {
                        $let: {
                            vars: {
                                matched: {
                                    $arrayElemAt: [ { $filter: { input: "$statuses", cond: { $eq: [ "$$ref", "$$this.name" ] } } }, 0 ]
                                }
                            },
                            in: {
                                $ifNull: [ "$$matched", { name: "$$ref", created_on: new Date() } ]
                            }
                        }
                    }
                }
            }
        }
    }
])

Mongo Playground

Collectives™ on Stack Overflow

Mongo DB compare two arrays and add missing element

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related