Can someone help me with this

Question

I actually need to use aggregation in mongoDb. I have already used the following query for joining two collection and then extracting the answer by some filters. The query is working fine in mongodb terminal.

But I need to use this query in springboot using aggreagte function in Spring boot. I looked at the below documentation, which was very usefull. But as the query is very big. That's why I'm unable to write it in the proper way.

https://www.baeldung.com/spring-data-mongodb-projections-aggregations

db.coll1.aggregate([
  {
    "$unionWith": {"coll": "coll2"}
  },
  {
    "$group": {
      "_id": {
        "Id": "Id",
        "Name": {
          "$arrayElemAt": [
            {
              $split: [
                "FullName",
                "T",
              ]
            },
            1
          ]
        },
        "StreetName": {
          "$arrayElemAt": [
            {
              $split: [
                "Address",
                "T",
                
              ]
            },
            1
          ]
        }
        
      },
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$match": {
      "$expr": {
        "$gt": [
          "$count",
          1
        ]
      }
    }
  }])

Can you please help me to use the aggreagte function and convert this query so that I can you this in springboot.

Answer 1

looks like trying to do a count(*), group by trunc(gapStartTimeStamp), trunc(gapEndTimeStamp) where count > 1

If that's the case: a slight variation on your query for that, tested with spring-data-mongodb 3.1.8

---

UnionWithOperation unionWith = UnionWithOperation.unionWith("coll2");
        
ProjectionOperation convertStartDateOp = Aggregation.project("stationId", "gapStartTimeStamp", "gapEndTimeStamp")
            .and(StringOperators.Substr.valueOf("gapStartTimeStamp").substring(0, 10))
                .as("gapStartDate")
                .and(StringOperators.Substr.valueOf("gapEndTimeStamp").substring(0, 10))
                .as("gapEndDate");
                
GroupOperation countOp = Aggregation.group("stationId", "gapStartDate", "gapEndDate").count().as("count");
        MatchOperation matchOp = Aggregation.match(Criteria.where("count").gt(1));

AggregationResults<Document> aggregate = mongoOps.aggregate(
        Aggregation.newAggregation(
            unionWith,convertStartDateOp,countOp, matchOp
        ),
        YourColl1.class, Document.class);

List<Document> mappedResults = aggregate.getMappedResults();

---

generates this query:

db.coll1.aggregate(
[
 {"$unionWith": {"coll": "coll2"}}, 
  {"$project": 
    {
      "stationId": 1, 
      "gapStartTimeStamp": 1, 
      "gapEndTimeStamp": 1, 
      "gapStartDate": {"$substr": ["$gapStartTimeStamp", 0, 10]}, 
      "gapEndDate": {"$substr": ["$gapEndTimeStamp", 0, 10]}
    }
  }, 
  {"$group": {
    "_id": {
      "stationId": "$stationId", 
      "gapStartDate": "$gapStartDate", 
      "gapEndDate": "$gapEndDate"
    }, 
    "count": {"$sum": 1}
   }
  }, 
  {"$match": {"count": {"$gt": 1}}}
  
  ]
  );