mongodbspring-data-mongodbspring-mongo

Spring Data Mongo DB Criteria query to filter nested documents based on condition


I am working on fetching documents which has nested documents also to it using Spring Data Mongo. I am trying to get documents and include nested documents to it if a particular condition matches or give me just the parent document alone. I need the parent document and nested document which matches condition if any of the replies, isApproved status is true or else just get the parent document alone. Below is the sample data set ,

Sample Data Set:
{
"_id" : ObjectId("5efe3d1f8a2ef008249f72d9"),
"_class" : "com.text.TestInfo",
"discussion_id" : "5efcbbdee87e6d1a706d3a4a",
"content" : "edited comment",
"title" : "Test",
"isApproved" : true,
"replies" : [
  {
  "_id" : ObjectId("59nts64j3cdcds3449f72d9"),
  "content" : "Sample text",
  "isApproved" : false
  },
  {
  "_id" : ObjectId("0j93k099vcdkfdf3vdf372d9"),
  "content" : "Sample text",
  "isApproved" : true
  }   
  ]
 },
 {
"_id" : ObjectId("5efe3d189ms908249f72d9"),
"_class" : "com.text.TestInfo",
"discussion_id" : "5efcbbdee87e6d1a706d3a4a",
"content" : "new eidted comment",
"title" : "Test",
"isApproved" : true,
"replies" : [
  {
  "_id" : ObjectId("59nts64j3cdcds3449f72d9"),
  "content" : "Sample text",
  "isApproved" : false
  }
  ]
 },
 {
"_id" : ObjectId("5efe390d9sds249f72d9"),
"_class" : "com.text.TestInfo",
"discussion_id" : "5efcbbdee87e6d1a706d3a4a",
"content" : "new eidted comment",
"title" : "Test",
"isApproved" : false,
"replies" : [
  {
  "_id" : ObjectId("59nts64j3cdcds3449f72d9"),
  "content" : "dasdsatext",
  "isApproved" : true
  }
  ]
 }

So, above is the data set in one document there is two sub-document and in next one there is only one sub-document. Below is the expected output.

Expected Output:
{
"_id" : ObjectId("5efe3d1f8a2ef008249f72d9"),
"_class" : "com.text.TestInfo",
"discussion_id" : "5efcbbdee87e6d1a706d3a4a",
"content" : "edited comment",
"title" : "Test",
"isApproved" : true,
"replies" : [
  {
  "_id" : ObjectId("0j93k099vcdkfdf3vdf372d9"),
  "content" : "Sample text",
  "isApproved" : true
  }   
  ]
},
{
"_id" : ObjectId("5efe3d189ms908249f72d9"),
"_class" : "com.text.TestInfo",
"discussion_id" : "5efcbbdee87e6d1a706d3a4a",
"content" : "new eidted comment",
"title" : "Test",
"replies" : []
}

Below are the 2 ways which I have tried using Spring Data MongoDB

First Way:
Query query = new Query();
    Criteria criteria = new Criteria().andOperator(Criteria.where("discussion_id").
    is(new ObjectId("5efe3d1f8a2ef008249f72d9"))).and("replies").elemMatch(Criteria.where("isApproved").ne(true));
    query.addCriteria(criteria);
    List<TestInfo> listOfInfos = mongoOps.find(query, TestInfo.class);

What I received as a result is first document completely alone which doesn't suffice my requirement.

Second Way:
Query query = new Query();
    Criteria criteria = new Criteria().andOperator(Criteria.where("discussion_id").
    is(new ObjectId("5efe3d1f8a2ef008249f72d9")));
    query.addCriteria(criteria);
    query.fields().elemMatch(Criteria.where("isApproved").ne(true));
    List<TestInfo> listOfInfos = mongoOps.find(query, TestInfo.class);

Here the output is only the reply sub document present in first document parent document values are coming as null.

Kindly, let me know the what is the mistake I made


Solution

  • You need to use $project. To return only the matched array element, use replies.$

    play

    db.collection.find({
      "replies.isApproved": false
    },
    {
      "replies.$": 1,
      "_class": 1,
      "discussion_id": 1
    })
    

    To get other elements where array doesn't have match:

    play

    db.collection.find({},
    {
      "replies": {
        $elemMatch: {
          "isApproved": false
        }
      },
      "_class": 1,
      "discussion_id": 1
    })
    

    Edit:

    play

    db.collection.find({
      "isApproved": false
    },
    {
      "replies": {
        $elemMatch: {
          "isApproved": false
        }
      },
      "_class": 1,
      "discussion_id": 1
    })