Mongo Query to return common values in array

Question

I need a Mongo Query to return me common values present in an array. So if there are 4 documents in match, then the values are returned if those are present in in all the 4 documents

Suppose I have the below documents in my db

Mongo Documents

    {
    "id":"0",
    "merchants":["1","2"]
    }

    {
    "id":"1",
    "merchants":["1","2","4"]
    }

    {
    "id":"2",
    "merchants":["4","5"]
    }

Input : List of id

(i) Input with id "0" and "1"

Then it should return me merchants:["1","2"] as both are present in documents with id "0" & id "1"

(ii) Input with id "1" and "2" Then it should return me merchants:["4"] as it is common and present in both documents with id "1" & id "2"

(iii) Input with id "0" and "2" Should return empty merchants:[] as no common merchants between these 2 documents

What is your MongoDB server version?

chridam
– chridam

2017-02-17 16:17:15 +00:00
Commented Feb 17, 2017 at 16:17 — chridam
– chridam, Commented Feb 17, 2017 at 16:17

s7vr · Accepted Answer · 2017-02-17 16:27:33Z

3

You can try below aggregation.

db.collection.aggregate(
   {$match:{id: {$in: ["1", "2"]}}},
   {$group:{_id:null, first:{$first:"$merchants"}, second:{$last:"$merchants"}}},
   {$project: {commonToBoth: {$setIntersection: ["$first", "$second"]}, _id: 0 } }
)

answered Feb 17, 2017 at 16:27

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

3 Comments

jgr0 Over a year ago

This won't work in the case where more than two ids are inputted

Akshay Mehta Over a year ago

Yes it does not work. This query just considers the first and last document and ignores the intermediate documents

s7vr Over a year ago

Its obvious it doesn't work if you look at the labels first and second . I provided answer based on your sample inputs. Consider updating your post to tell exactly what you're looking for.

jgr0 · Accepted Answer · 2017-02-18 06:10:09Z

2

Say you have a function query that does the required DB query for you, and you'll call that function with idsToMatch which is an array containing all the elements you want to match. I have used JS here as the driver language, replace it with whatever you are using.

The following code is dynamic, will work for any number of ids you give as input:

const query = (idsToMatch) => {

    db.collectionName.aggregate([
        { $match: { id: {$in: idsToMatch} } },
        { $unwind: "$merchants" },
        { $group: { _id: { id: "$id", data: "$merchants" } } },
        { $group: { _id: "$_id.data", count: {$sum: 1} } },
        { $match: { count: { $gte: idsToMatch.length } } },
        { $group: { _id: 0, result: {$push: "$_id" } } },
        { $project: { _id: 0, result: "$result" } }    
    ])

The first $group statement is to make sure you don't have any repetitions in any of your merchants attribute in a document. If you are certain that in your individual documents you won't have any repeated value for merchants, you need not include it.
The real work happens only upto the 2nd $match phase. The last two phases ($group and $project) are only to prettify the result, you may choose not use them, and instead use the language of your choice to transform it in the form you want

Assuming you want to reduce the phases as per the points given above, the actual code will reduce to:

aggregate([
        { $match: { id: {$in: idsToMatch} } },
        { $unwind: "$merchants" },
        { $group: { _id: "merchants", count: {$sum: 1} } },
        { $match: { count: { $gte: idsToMatch.length } } }   
    ])

Your required values will be at the _id attribute of each element of the result array.

edited Feb 18, 2017 at 6:10

answered Feb 17, 2017 at 18:43

jgr0

7272 gold badges7 silver badges21 bronze badges

2 Comments

Akshay Mehta Over a year ago

This too does not work as the intermediate match operation incorrect. The intermediate match operation should be { $match: { count: { $gt: "no_of_ids_in_1st_match_operation" } } }

jgr0 Over a year ago

Solving that part is trivial. Say you have a function query that does this DB query for you, and you call it like: query(idsToMatch), where idsToMatch is an array of ids you want to match. You can simple replace the $gt:1 part to $gt: (idsToMatch.length - 1) (assuming ur using js, otherwise use your language's length function).

Akshay Mehta · Accepted Answer · 2017-02-18 08:03:49Z

The answer provided by @jgr0 is correct to some extent. The only mistake is the intermediate match operation

(i) So if input ids are "1" & "0" then the query becomes

     aggregate([
            {"$match":{"id":{"$in":["1","0"]}}},
            {"$unwind":"$merchants"},
            {"$group":{"_id":"$merchants","count":{"$sum":1}}},
            {"$match":{"count":{"$eq":2}}},
            {"$group":{"_id":null,"merchants":{"$push":"$_id"}}},
            {"$project":{"_id":0,"merchants":1}} 
            ])

(ii) So if input ids are "1", "0" & "2" then the query becomes

    aggregate([
        {"$match":{"id":{"$in":["1","0", "2"]}}},
        {"$unwind":"$merchants"},
        {"$group":{"_id":"$merchants","count":{"$sum":1}}},
        {"$match":{"count":{"$eq":3}}},
        {"$group":{"_id":null,"merchants":{"$push":"$_id"}}},
        {"$project":{"_id":0,"merchants":1}} 
        ])

The intermediate match operation should be the count of ids in input. So in case (i) it is 2 and in case (2) it is 3.

Collectives™ on Stack Overflow

Mongo Query to return common values in array

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related