Agrregation using mongoDBtemplate for Nested document

Question

I am trying to use agrregation using spring mongodb template. The grouping has to be done on third level of document. input document is

{
    "_id": "59036b0fa036cc28c8e07db6",
    "sections": [{
        "srcName": "test1",
        "data": [{
                "srcKey": "",
                "rowIdx": 0,
                "values": [{
                        "srcDesc": "Assets"
                    },
                    {
                        "srcDesc": "NonAssets"
                    },
                    {
                        "srcDesc": "liabilities"
                    }
                ]
            },
            {
                "srcKey": "01",
                "rowIdx": 1,
                "values": [{
                    "srcDesc": "NonAssets"
                }]
            }
        ]
    }]
}

Essentially I want to run query like

select distinct(srcdesc) from document where srcName="test1";

Please see that srcDesc is third level of nesting. I am trying below java code

  private MatchOperation getMatchOPeration(String sectionName){
      Criteria criteira=Criteria.where("sectionName").in(sectionName);
      return match(criteira);
  }

  private GroupOperation getGroupOperaion(){
      return  group("srcDesc").last("srcDesc").as("srcDesc"); 
  }

 private ProjectionOperation  getProjectionOPeration(){
      return project("srcDesc").and("srcDesc").previousOperation();
  }

 public List<SourceName> findAllSourceNamesBySection(String sectionName){
       List<SectionsDocument> sourceNameList=new ArrayList<>();
       MatchOperation matchOPeration=getMatchOPeration(sectionName);
       GroupOperation groupOperation=getGroupOperaion();
       ProjectionOperation projectionOperation=getProjectionOPeration();
       AggregationResults<SectionsDocument> aggregationResults=
       mongoTemplate.aggregate(Aggregation.newAggregation(
            matchOPeration,
            unwind("sections.data.values"),
            groupOperation,
            projectionOperation),StatDocument.class,SectionsDocument.class);
            sourceNameList=aggregationResults.getMappedResults();   
       return new ArrayList<>();
  }

@veeram ,I am using org.springframework.boot spring-boot-starter-data-mongodb 1.5.1.RELEASEorg.springframework.boot spring-boot-starter-data-mongodb 1.5.1.RELEASE — athenatechie, May 07 '17 at 19:59

s7vr · Accepted Answer · 2017-05-08T18:52:46.487

You can update your code to below.

Added $unwind to unwind sections & sections.data before unwinding sections.data.values.

Updated $group get distinct srcDesc with $addToSet operator.

Removed the $project stage.

private  MatchOperation getMatchOperation(String sectionName){
    Criteria criteira=Criteria.where("sections.srcName").in(sectionName);
    return match(criteira);
}

private  GroupOperation getGroupOperaion(){
    return  group().addToSet("sections.data.values.srcDesc").as("srcDescs");
}

public List<String> findAllSrcDescBySection(String sectionName){
    MatchOperation matchOperation=getMatchOperation(sectionName);
    GroupOperation groupOperation=getGroupOperaion();
    BasicDBObject aggregationResults=
               mongoTemplate.aggregate(Aggregation.newAggregation(
                       matchOperation,
                       unwind("sections"),
                       unwind("sections.data"),
                       unwind("sections.data.values"),
                       matchOperation,
                       groupOperation), collectionname, BasicDBObject.class).getUniqueMappedResult();
   return (ArrayList)aggregationResults.get("srcDescs");
}

Update 3.4 version:

This is similar to above but instead of $unwind'ing, we are $reduce'ing to $project srcDescs from each document.

$map + $filter to filter the sections elements with input srcName followed by map to transform the output. $reduce will use the output from filter stage inside the map stage to extract and merge ( $concatArrays part) srcDesc across all values in each data.

$unwind + $group to get distinct srcDesc with $addToSet operator.

aggregate([{
        $project: {
            srcDescs: {
                $arrayElemAt: [{
                    $map: {
                        input: {
                            $filter: {
                                input: "$sections",
                                as: "sectionsf",
                                cond: {
                                    $eq: ["$$sectionsf.srcName", "test1"]
                                }
                            }
                        },
                        as: "sectionsm",
                        in: {
                            $reduce: {
                                input: "$$sectionsm.data",
                                initialValue: [],
                                in: {
                                    $concatArrays: ["$$value", "$$this.values.srcDesc"]
                                }
                            }
                        }
                    }
                }, 0]
            }
        }
    },
    {
        $unwind: "$srcDescs"
    },
    {
        $group: {
            _id: null,
            srcDescs: {
                $addToSet: "$srcDescs"
            }
        }
    }
])

@ Veeram , Thank you. I will try this today. Also, I am new to all this. Do you have any links to good documentation that can help me on these unwind concept or addtoSet concept. — athenatechie, May 08 '17 at 10:56
You are welcome. Here are links https://docs.mongodb.com/manual/reference/operator/aggregation/addToSet/ & https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/. You can improve your aggregation pipeline if you are on mongo server 3.4 version. Let me know what version you are on and I can update the answer. — s7vr, May 08 '17 at 12:48
Yes . We are at db version v3.4.3. Appreciate your help on this. — athenatechie, May 08 '17 at 14:19
You are welcome. Please try to test the current changes and will try to update changes for 3.4 — s7vr, May 08 '17 at 14:25
Unfortunately the results did provided me with unique results. May be we are missing something here. Below is the out put — athenatechie, May 08 '17 at 15:26
Sorry, I had a typo. The solution worked. Now , i need to understand it properly how it worked. — athenatechie, May 08 '17 at 15:38
Np. Please feel free to ask if you have any questions and I can add more details. Also, I've added 3.4 mongo shell query. See if you can convert it to spring equivalent. — s7vr, May 08 '17 at 18:54

Agrregation using mongoDBtemplate for Nested document

1 Answers1