CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
venkat
venkat

Reputation: 69

Retrive nested array document

    {

        "id" : "Sir3GHMQ",
        "name" : "Medavakkam",
        "userList" : [ 
            {
                "loginName" : "[email protected]",
                "role" : "ADMIN"
            }, 
            {
                "loginName" : "[email protected]",
                "role" : "Operator"
            }
        ]
    }


    {

        "id" : "Sir3GHER",
        "name" : "Medavakkam",
        "userList" : [ 
            {
                "loginName" : "[email protected]",
                "role" : "OPERATOR"
            },
{
                "loginName" : [email protected]",
                "role" : "OPERATOR"
            }
        ]
    }

In the collection I need to retrieve documents where userList. loginame="[email protected]" also check where role is "admin". Where role is admin means retrieve all userList:LoginName with their role, else retrieve only userList:loginName with their role, what ever it is.

I tried this: 

db.Site.aggregate([ 
    { "$match": { "userList.loginName": "[email protected]" } }, 
    { "$redact": { 
        "$cond": [ 
            { "$eq": [ 
                { "$ifNull" [ "$loginName", "[email protected]" ] }, 
                "[email protected]" 
            ] }, 
            "$$DESCEND", 
            "$$PRUNE" 
        ] 
    } } 
])

i need output like this

{

    "id" : "Sir3GHMQ",
    "name" : "Medavakkam",
    "userList" : [ 
        {
            "loginName" : "[email protected]",
            "role" : "ADMIN"
        }, 
        {
            "loginName" : "[email protected]",
            "role" : "Operator"
        }
    ]
}


{

    "id" : "Sir3GHER",
    "name" : "Medavakkam",
    "userList" : [ 
        {
            "loginName" : "[email protected]",
            "role" : "OPERATOR"
        }

    ]
}

Upvotes: 0

Views: 227

Answers (1)

radhakrishnan
radhakrishnan

Reputation: 1459

Use this command , 

          db.f.aggregate([{
            $match: {
                "userList.loginName": "[email protected]"
            }
        }, {
            "$redact": {
                "$cond": [{
                    $or: [{
                        "$eq": [{
                                "$ifNull": ["$loginName", "[email protected]"]
                            },
                            "[email protected]"
                        ]
                    }, {
                        "$eq": [{
                            $setIsSubset: [{
                                $literal: [{
                                    loginName: "[email protected]",
                                    role: "ADMIN"
                                }]
                            }, "$$ROOT.userList"]
                        }, true]
                    }]
                }, "$$DESCEND", "$$PRUNE"]
            }
        }]).pretty()

OutputData:

      {
    "_id" : ObjectId("58b697e406169b8451ba4cd2"),
    "id" : "Sir3GHMQ",
    "name" : "Medavakkam",
    "userList" : [
            {
                    "loginName" : "[email protected]",
                    "role" : "ADMIN"
            },
            {
                    "loginName" : "[email protected]",
                    "role" : "Operator"
            }
    ]
   }

  {
    "_id" : ObjectId("58b74e91c568ace843ee17c1"),
    "id" : "Sir3GHER",
    "name" : "Medavakkam",
    "userList" : [
            {
                    "loginName" : "[email protected]",
                    "role" : "OPERATOR"
            }
    ]
 }

Hope this will help you.

Java code:

              MongoClient mongoClient = new MongoClient();
              MongoDatabase database = mongoClient.getDatabase("test");
              MongoCollection<Document> collection = database.getCollection("f");

                List<Document> results = collection.aggregate(Arrays.asList(new Document("$match",new Document().append("userList.loginName", "[email protected]")),
                        new Document("$redact", new Document("$cond",
                                Arrays.asList(new Document("$or",Arrays.asList(new Document("$eq",
                                        Arrays.asList(new Document("$ifNull", Arrays.asList("$loginName", "[email protected]")), "[email protected]")),new Document("$eq", Arrays.asList(new Document("$setIsSubset", Arrays.asList(new Document("$literal", Arrays.asList(new Document().append("loginName", "[email protected]").append("role", "ADMIN"))),"$$ROOT.userList")), true)))), 
                                "$$DESCEND", "$$PRUNE")))

            )).into(new ArrayList<Document>());

                for(Document docs: results){
                    System.out.println(docs.toJson());
                }

Upvotes: 3

Related Questions