CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
exonyz
exonyz

Reputation: 61

Filter arrays in mongodb

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.

Upvotes: 1

Views: 97

Answers (2)

Anastasia
Anastasia

Reputation: 11

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. :)

Upvotes: 0

turivishal
turivishal

Reputation: 36094

  • $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

Upvotes: 2

Related Questions