I have two collections: Company:
{
"_id" : "1",
"company_code" : "A-111",
"code_description" : "BGI",
"out_company_code" : "A-111"
}
Cost center:
{
"_id" : "2,
"company_id" : "1", //Company id
"home_cost_center" : "1111",
"home_cost_center_description" : "Finance"
}
I wanted to have a query to result like below:
{
"_id" : "2,
"company_id" : "1",
"home_cost_center" : "1111",
"home_cost_center_description" : "Finance",
"company": {
"_id" : "1",
"company_code" : "A-111",
"code_description" : "BGI",
"out_company_code" : "A-111"
}
}
For achieving this, I created this query
String queryWithCompanyCode = " { '$lookup': {" +
" 'from': 'company'," +
" 'let': { 'codeId': '$company_id' }," +
" 'pipeline': [" +
" { '$match': { '$expr': { '$eq': [{ '$toString': '$_id' }, '$$codeId'] }}}" +
" ]," +
" 'as': 'companyCode'" +
" }}," +
" {'$unwind': '$companyCode'}";
ObjectId objectId = new ObjectId(id);
AggregationOperation operation = Aggregation.match(
Criteria.where("_id").is(objectId)
);
TypedAggregation<T> typedAggregation = Aggregation.newAggregation(Output.class, operation, new CustomAggregationOperation(queryWithCompanyCode));
T result = operations.aggregate(typedAggregation, CostCenter.class, Output.class).getUniqueMappedResult();
With the above query I am getting this result:
{
"_id" : "2,
"company_id" : null,
"home_cost_center" : null,
"home_cost_center_description" : null,
"company": {
"_id" : "1",
"company_code" : "A-111",
"code_description" : "BGI",
"out_company_code" : "A-111"
}
}
I am using Spring: 2.1.10 and mongodb: 4.2.1. It's strange that when I run this query in the database then it works fine but doesn't work in spring and I can't figure out what's wrong here.