0

I have below SQL query.

 List<EmployeeInfo> employeesInfoList = Lists.newArrayList();
        final StringBuilder query = new StringBuilder(265);
        query.append("employeeKey.code = ? AND ");
        query.append("employeeKey.month = ? AND ");
        query.append("payProjection.details[*]( month = ? AND netValue != ? ) AND ");
        query.append("status.active = ? AND ");
        query.append("status.type = ? ");
        query.append("GROUP BY employeeKey.employeeNumber ");
        query.append("ORDER BY employeeKey.employeeNumber ");
        final SQLQuery<EmployeeInfo> sqlQuery;    
        sqlQuery = getSQLQuery(query.toString(), code, month, month, 0, true, type);    
        sqlQuery.setProjections("employeeKey", "status", "payProjection");

The above query has to be converted to Spring DATA mongo query.

I tried below options but not working.

options1.

final Criteria criteria = where(employeeKey.code).is(code)
            .andOperator(where(employeeKey.month).is(month),
                where("payProjection.details.month").is(month),
                where("payProjection.details.netValue").ne(0),
                where(status.active).in(true), where(status.type).in(type));
        final Query query = new Query(criteria);
        query.fields().include(employeeKey).include(status).include("payProjection");
        query.with(new Sort(Sort.Direction.ASC, "employeeKey.employeeNumber "));
        return mongoTemplate.find(query, EmployeeInfo.class);

Also I dont know how to fit group by employeekey.employeenumber in the above mongo query. help me to fix this issue.

Options2.

 final AggregationOperation match = Aggregation.match( where(employeeKey.code).is(code)
            .andOperator(where(employeeKey.month).is(month),
                where("payProjection.details.month").is(month),
                where("payProjection.details.netValue").ne(0),
                where(status.active).in(true), where(status.type).in(type)););
        final AggregationOperation group = Aggregation.group("employeeKey.employeeNumber");
        final AggregationOperation sort = Aggregation.sort(Sort.Direction.ASC, "employeeKey.employeeNumber");
        final Aggregation aggregation = Aggregation.newAggregation(Aggregation.unwind("employeeKey.employeeNumber"), match, group, sort);
        return mongoTemplate.aggregate(aggregation, EmployeeInfo.class, EmployeeInfo.class).getMappedResults();

My Objects structure:

public class EmployeeInfo implements Serializable {

    private static final long serialVersionUID = 1060791921216076800L;

    @Id
    private EmployeeKey employeeKey;

    private Status status;

    private PayProjection payProjection;   

}
EmployeeKey{
 private String code;    
    private int employeeNumber;
    private String month;
    }

    Status{
     private boolean active;
      private Type type;
      }

      PayProjection{
      List<Details> details
      }

      Details{
    private String month;
    private int netValue;}
Rithik_Star
  • 651
  • 5
  • 14
  • 39
  • Can you add result or error you are getting? Also, add aggregation query you are trying to convert to spring mongo. – Rajat Goel Jun 15 '19 at 05:04
  • @no error its not returning results – Rithik_Star Jun 15 '19 at 05:18
  • Then the only case is query you are forming is wrong. Please share sample data and what you are trying to accomplish with query – Rajat Goel Jun 15 '19 at 05:22
  • @RajatGoel How to look for a value inside a list . ? In the above scenario whether the below are correctly given or not? "payProjection.details.netValue" and "payProjection.details.month" . details is a list in payprojection. – Rithik_Star Jun 15 '19 at 08:42
  • check $elemMatch. "payProjection.details.netValue" and "payProjection.details.month" will also work. but not properly. check - https://stackoverflow.com/questions/14040562/how-to-search-in-array-of-object-in-mongodb – Rajat Goel Jun 15 '19 at 09:09

0 Answers0