MongoDB update if value from one array is in other array

Question

I have a collection (1) with the following field:

{
 _id: 1,
 value: ["A","B","C","D"]
}

and inside another collection (2), I have the following fields:

{
 _id: 1,
 predicted_value: [
  {
   value: "A",
   num: 1,
  },
  {
   value: "B",
   num: 2,
  },       
  {
   value: "D",
   num: 2
  }
 ],
 real_prediction: <Collection1.value>
 predicted_by: "Person1",
 is_correct_prediction: <bool>
}

My goal is now to update the second collection by the following: If an entry gets put into value of collection 1 (I already am listening to this type of event), collection 2 should update the real prediction by gathering the values of collection 1 and update the boolean is_correct_prediction, if one element in the value array is also in the predicted_value array. In this case it should be set to True, by default it is False. Is there some sort of mongo query, that can manage this? It can definetly be done with Python, but is it possible to also use mongo only?

ray · Accepted Answer · 2022-12-15 21:31:41Z

1

You can perform a $lookup to collection 1 and use $setIntersection to check whether is_correct_prediction should be true.

db.coll2.aggregate([
  {
    "$lookup": {
      "from": "coll1",
      "localField": "_id",
      "foreignField": "_id",
      "as": "coll1Lookup"
    }
  },
  {
    "$unwind": "$coll1Lookup"
  },
  {
    $set: {
      real_prediction: "$coll1Lookup.value",
      is_correct_prediction: {
        $ne: [
          [],
          {
            "$setIntersection": [
              "$coll1Lookup.value",
              "$predicted_value.value"
            ]
          }
        ]
      }
    }
  },
  {
    $unset: "coll1Lookup"
  },
  {
    "$merge": {
      "into": "coll2",
      "on": "_id"
    }
  }
])

Mongo Playground

answered Dec 15, 2022 at 21:31

ray

15.6k12 gold badges97 silver badges105 bronze badges

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

5 Comments

max Over a year ago

I will definetly try this out! Thanks a lot! One last question, I found out that lookup only allows the same collection, is there an option to access a collection from another database?

ray Over a year ago

@AGI_Max What do you mean by lookup only allows the same collection? $lookup is meant to perform data correlation between different collections.

max Over a year ago

I used the wrong term, my bad. I meant lookups on a different database.

ray Over a year ago

@AGI_Max Currently MongoDB does not support cross-database lookup. You may want to check out this feature request for the status update.

max Over a year ago

Ok thank you very much! The query helped a lot. I still needed to use a little bit of python, but with way less loops :)

Collectives™ on Stack Overflow

MongoDB update if value from one array is in other array

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related