0

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.

Jennifer
  • 107
  • 1
  • 1
  • 5

0 Answers0