Filter arrays in mongodb

Question

I have a collection where each document contains 2 arrays of documents as below.

{
  all_users:[
    {
      id:1,
      name:"A"
    },
    {
      id:2,
      name:"B"
    },
    {
      id:3,
      name:"C"
    }
  ]
  selected_users:[
    {
     id:1,
      name:"A"
    },
    {
      id:2,
      name:"B"
    }
  ]
}

Is it possible to merge the 2 arrays by adding a field selected and assigning a value to it based on whether the name of the user is present in a document in the selected_users array as follows.

{
  all_users:[
    {
      id:1,
      name:"A",
      selected:"yes"
    },
    {
      id:2,
      name:"B",
      selected:"yes"
    },
    {
      id:3,
      name:"C",
      selected:"no"
    }
  ]
}

I want to check by id or name since the documents may contain additional fields. I can't figure out how to achieve this.

turivishal · Accepted Answer · 2021-06-03 10:50:38Z

2

$map to iterate loop of all_users array
$cond check condition if id is in selected users id then return "yes" otherwise "no" in selected field
$mergeObject to merge current user object with above selected field

db.collection.aggregate([
  {
    $project: {
      all_users: {
        $map: {
          input: "$all_users",
          in: {
            $mergeObjects: [
              "$$this",
              {
                selected: {
                  $cond: [
                    { $in: ["$$this.id", "$selected_users.id"] },
                    "yes",
                    "no"
                  ]
                }
              }
            ]
          }
        }
      }
    }
  }
])

Playground

answered Jun 3, 2021 at 10:50

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

exonyz Over a year ago

Thank you! It works. If I had to project, say, only name and selected fields, what changes would have to be made?

turivishal Over a year ago

you can simply return your needed fields see playground

Anastasia · Accepted Answer · 2021-06-03 11:05:07Z

0

If you are using the Robo Mongo 3T, you can use JavaScript, so you can do something like that:

db.collection.find({}).forEach(doc => {
    doc.all_users.map(au => {
        const su = doc.selected_users.find(su => su.id === au.id);
        au.selected = su === undefined; 
    });
    delete doc.selected_users;
    db.collection.save(doc);
 })

I would advise you to create a Backup of the collection beforehand. :)

answered Jun 3, 2021 at 11:05

Anastasia

112 bronze badges

Collectives™ on Stack Overflow

Filter arrays in mongodb

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related