I use: 1. Mongo 3.4v database. (I am new in mongo) 2. Spring boot 1.5.1.RELEASE (spring data mongodb 1.10.0.RELEASE)
I have database with two collections 'categories' and 'ads' (advertisements)
Categories
{
"_id" : ObjectId("58a4b39eca0cdc1ba88d9bf5"),
"_class" : "com.mongotest.model.Category",
"title" : {
"en" : "My title"
},
"parentId" : ObjectId("58b1fca82dc4b81365110fe4")
}
Ads
{
"_id" : ObjectId("58a62e4a27dccb1bec742052"),
"_class" : "com.mongotest.model.Ads",
"categoryId" : ObjectId("58a4b39eca0cdc1ba88d9bf5"),
"description" : "Description",
"phone" : "29890000",
"email" : "[email protected]",
"price" : "65.0"
}
I have created mongo query which return for each category array of subcategories and count of ads for each subcategory.
This mongo query looks like this (working 100% i was double check it before question):
db.categories.aggregate([
{
$match : {
"parentId": null
}
},
{
$lookup:{
from: "categories",
localField: "_id",
foreignField: "parentId",
as: "subcategories"
}
},
{
$unwind : {
path : "$subcategories",
preserveNullAndEmptyArrays : true
}
},
{
$lookup:{
from: "ads",
localField: "subcategories._id",
foreignField: "categoryId",
as: "subcategories.adsList"
}
},
{
$project : {
"title" : "$title",
"subcategories._id" : "$subcategories._id",
"subcategories.title" : "$subcategories.title",
"subcategories.adsCount" : {$size : "$subcategories.adsList"}
}
},
{
$group : {
"_id" : "$_id",
"title" : { $first : "$title" },
"subcategories" : { $push: "$subcategories" }
}
}])
Here is my translation to Spring mongo data Aggregation framework:
AggregationOperation match = Aggregation.match(Criteria.where("parentId").is(null));
AggregationOperation lookup = Aggregation.lookup("categories", "_id", "parentId", "subcategories");
AggregationOperation unwindSubcategories = Aggregation.unwind("subcategories", true);
AggregationOperation lookupAds = Aggregation.lookup("ads", "subcategories._id", "categoryId", "subcategories.adsList");
AggregationOperation project = Aggregation.project().and("subcategories._id").as("subcategories._id").and("title").as("title").and("subcategories.title").as("subcategories.title").and("subcategories.adsList").size().as("adsCount");
AggregationOperation group = Aggregation.group("_id").first("title").as("title").push("subcategories").as("subcategories");
Aggregation aggregation = Aggregation.newAggregation(match, lookup, unwindSubcategories, lookupAds, project, group); //Invalid reference 'subcategories'
AggregationResults<CategoryTO> aggregate = mongoTemplate.aggregate(aggregation, Category.class, CategoryTO.class);
Problem is in group operation
In this case i receive Invalid reverence 'subcategories'. I can't understand why 'subcategories' reference is unavailable in push() method if in project() operation it is available?!
How I can solve it?
Also i know about related issues Spring Data MongoDB - Aggregation Framework - invalid reference in group Operation
Spring Data MongoDB: aggregation framework - sort with nested property throws invalid reference
Many thanks for the feedback.