CODEWITHSUNDEEP
mongodbmongodb-query
Root
Root

Reputation: 147

MongoDB get unique document which need to be compared with other documents in the same collection

Below is my sample documents structure in the test collection.

problem description:

I have to get the name field from this document based on the below condition.

I will pass the role field as query criteria. It should fetch the name values which is not related to role in the document.

please consider below documents.If my query criteria is role r1 then the find query should return the names as:

{"name":"S"},{"name":"G"}, {"name":"H"}

because r1 is not assigned to this names in any of the documents in the collection.

The same rule is applicable for all roles like r2,r3...r7. so basically we have to look up all the documents in the collection and need to fetch names which is not assigned to any groups.

/* 0 */
    {
        "_id" : ObjectId("567286bda5543ad892916a49"),
        "name" : "A",
        "role" : "r1"
    }

    /* 1 */
    {
        "_id" : ObjectId("567286bda5543ad892916a4a"),
        "name" : "A",
        "role" : "r2"
    }

    /* 2 */
    {
        "_id" : ObjectId("567286bda5543ad892916a4b"),
        "name" : "A",
        "role" : "r3"
    }

    /* 3 */
    {
        "_id" : ObjectId("567286bda5543ad892916a4c"),
        "name" : "B",
        "role" : "r2"
    }

    /* 4 */
    {
        "_id" : ObjectId("567286bda5543ad892916a4d"),
        "name" : "A",
        "role" : "r4"
    }

    /* 5 */
    {
        "_id" : ObjectId("567286bda5543ad892916a4e"),
        "name" : "B",
        "role" : "r1"
    }

    /* 6 */
    {
        "_id" : ObjectId("5672a8b7a5543ad892916a4f"),
        "name" : "S",
        "role" : "r3"
    }

    /* 7 */
    {
        "_id" : ObjectId("5672a8b7a5543ad892916a50"),
        "name" : "G",
        "role" : "r6"
    }

    /* 8 */
    {
        "_id" : ObjectId("5672a8b7a5543ad892916a51"),
        "name" : "H",
        "role" : "r2"
    }

Expected output:

-- input is role `r1` then (Need to show the name if `r1` is not associate already) -- same description for all the roles

    {"name" : "S"},
    {"name" : "G"},
    {"name" : "H"}

    -- input is role 'r2' then

    {"name" : "S"},
    {"name" : "G"}

    -- input is role 'r3' then

    {"name" : "B"},
    {"name" : "G"},
    {"name" : "H"}

    -- input is role 'r4' then

    {"name" : "B"},
    {"name" : "S"},
    {"name" : "G"},
    {"name" : "H"}

    -- input is role 'r6' then

    {"name" : "A"},
    {"name" : "B"},
    {"name" : "S"},
    {"name" : "H"}

any help is much appreciated. how to achieve this.aggregation framework or normal find query? please advice.

Upvotes: 2

Views: 105

Answers (2)

Isaiah4110
Isaiah4110

Reputation: 10100

I found a better way to get this result than what has been accepted as the answer and I think the performance would be better. Just check it out:

 db.collection.aggregate([
{ 
                  $group: { _id: null,             
                      rightrole: { $addToSet:  { $cond : {if: { $ne: [ "$role", "r1" ] }, then: "$name", else: null }} }, 
                      wrongrole: { $addToSet:  { $cond : {if: { $eq: [ "$role", "r1" ] }, then: "$name", else: null } } } } 
                          }, 
          {   "$project": {"_id": 0,         
                         "name": { "$setDifference": [ "$rightrole", "$wrongrole" ] } }
},              {$unwind: "$name"}
]);
  1. Find the list of names that are not in the current role and add it to an array.
  2. Find the list of names that are in the current role and add it to an array.
  3. Find the difference between the 2 arrays using the $setDifference operator.
  4. Unwind name

Upvotes: 1

hecnabae
hecnabae

Reputation: 407

You could try with aggregation: 

db.getCollection('roles').aggregate([
    {$group: {_id:"$name", "roles":{$addToSet:"$role"}}},
    {$project: {_id:1, "roles": 1, "isSubset": { $setIsSubset: [ ["r1"], "$roles" ] }}},
    {$match: {"isSubset": false}},
    {$group: {_id:"$isSubset", "name": {$push : "$_id"}}},
    {$project: {"_id": 0, "name": 1}},
    {$unwind: "$name"}
])
  1. Group by name, and push roles into an array.
  2. Create the "isSubset" field that indicates if your "role" is a subset from roles array".
  3. Search only those whose "isSubset" field is false
  4. Group by "isSubset"
  5. Show only "name" field.
  6. Unwind "name"

Upvotes: 0

Related Questions