Compass pipeline export to Java not producing same results

I am using compass to design the pipeline and export it to Java.
I am facing a problem when I use the exported pipeline in Java .
I am using
import org.springframework.data.mongodb.core.aggregation.*;
in Java .
Java 8, Spring boot data mongodb 2.3.1,
Compass 1.21.2 Mongodb Compass 4.2.8
I cant find the driver version . It must be the latest since I am using the latest from spring boot.
The problem is that the items that I push during grouping becomes True False values in the Java frame work. I cannot use the attributes name because of my client .

The pipeline is

    [{$match: {
  pd: 'PD',
  type: 'type1',
  date: {
    $gte: '2019-01-01',
    $lte: '2019-12-01'
  }
}}, {$sort: {
  pdg: 1,
  date: 1
}}, {$group: {
  _id: {
    site: '$site'
  },
  wf: {
    $push: {
      date: '$date',
      resources: '$resources',
      tt: '$n'
    }
  },
  tresources: {
    $sum: '$resources'
  }
}}, {$match: {
  tresources: {
    $gt: 0
  }
}}]

In Compass I get the results as

    [{
  "_id": {
    "site": "T"
  },
  "wf": [
    {
      "date": "2019-01-01",
      "resources": 2
    },
    {
      "date": "2019-02-01",
      "resources": 2
    },
    {
      "date": "2019-03-01",
      "resources": 2
    },
    {
      "date": "2019-04-01",
      "resources": 2
    },
    {
      "date": "2019-05-01",
      "resources": 2
    },
    {
      "date": "2019-06-01",
      "resources": 2
    },
    {
      "date": "2019-07-01",
      "resources": 2
    },
    {
      "date": "2019-08-01",
      "resources": 2
    },
    {
      "date": "2019-09-01",
      "resources": 2
    },
    {
      "date": "2019-10-01",
      "resources": 1
    },
    {
      "date": "2019-11-01",
      "resources": 1
    },
    {
      "date": "2019-12-01",
      "resources": 1
    }
  ],
  "tresources": 21
},{
  "_id": {
    "site": "G"
  },
  "wf": [
    {
      "date": "2019-01-01",
      "resources": 42
    },
    {
      "date": "2019-02-01",
      "resources": 42
    },
    {
      "date": "2019-03-01",
      "resources": 42
    },
    {
      "date": "2019-04-01",
      "resources": 39
    },
    {
      "date": "2019-05-01",
      "resources": 38
    },
    {
      "date": "2019-06-01",
      "resources": 38
    },
    {
      "date": "2019-07-01",
      "resources": 38
    },
    {
      "date": "2019-08-01",
      "resources": 41
    },
    {
      "date": "2019-09-01",
      "resources": 39
    },
    {
      "date": "2019-10-01",
      "resources": 34
    },
    {
      "date": "2019-11-01",
      "resources": 34
    },
    {
      "date": "2019-12-01",
      "resources": 31
    }
  ],
  "tresources": 458
}]


In Java Code

   List<Bson> bsons = Arrays.asList(
                    match(and(eq("pdg", "PD"), eq("type", "type1"),
                    and(gte("date", "2019-01-01"), lte("date", "2019-12-01")))),
                    sort(orderBy(ascending("pdg"), ascending("date"))),
                    group(eq("site", "$site"),
                            push("wf", and(eq("site", "$site"), eq("date", "$date"), eq("resources", "$resources"))),
                            sum("tresources", "$resources")),
                    match(gt("tresources", 0L)));
            template.getCollection("workforce").aggregate(bsons).forEach(document -> log.info(document.get("wf").toString()));

I get results as

   Document{{_id=Document{{site=T}}, wf=[true, true, true, true, true, true, true, true, true, true, true, true], tresources=21.0}}
Document{{_id=Document{{site=G}}, wf=[true, true, true, true, true, true, true, true, true, true, true, true], tresources=458.0}}

I cannot find any other way to do group based queries in Java . It would be nice to know How can I capture this output to custom Java Objects. I could do that using @Query annotation in the repository but it can only handle simple queries The same goes for template.aggregate and template.find.

Collection sample


[{
“_id”: “-1693282609”,
“comments”: “mongoupload”,
“date”: “2019-11-01”,
“site”: “T”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 1
}, {
“_id”: “-1694086966”,
“comments”: “mongoupload”,
“date”: “2019-05-01”,
“site”: “T”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 2
},{
“_id”: “-1694027384”,
“comments”: “mongoupload”,
“date”: “2019-07-01”,
“site”: “T”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 2
},{
“_id”: “-1693967802”,
“comments”: “mongoupload”,
“date”: “2019-09-01”,
“site”: “T”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 2
},{
“_id”: “-1693997593”,
“comments”: “mongoupload”,
“date”: “2019-08-01”,
“site”: “T”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 2
},{
“_id”: “-1694176339”,
“comments”: “mongoupload”,
“date”: “2019-02-01”,
“site”: “T”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 2
},{
“_id”: “-1694057175”,
“comments”: “mongoupload”,
“date”: “2019-06-01”,
“site”: “T”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 2
},{
“_id”: “-1694116757”,
“comments”: “mongoupload”,
“date”: “2019-04-01”,
“site”: “T”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 2
},{
“_id”: “-1693312400”,
“comments”: “mongoupload”,
“date”: “2019-10-01”,
“site”: “T”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 1
},{
“_id”: “-1694146548”,
“comments”: “mongoupload”,
“date”: “2019-03-01”,
“site”: “T”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 2
},{
“_id”: “-1693252818”,
“comments”: “mongoupload”,
“date”: “2019-12-01”,
“site”: “T”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 1
},{
“_id”: “-1694206130”,
“comments”: “mongoupload”,
“date”: “2019-01-01”,
“site”: “T”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 2
},{
“_id”: “-1840908577”,
“comments”: “mongoupload”,
“date”: “2019-03-01”,
“site”: “G”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 42
},{
“_id”: “-1840044638”,
“comments”: “mongoupload”,
“date”: “2019-11-01”,
“site”: “G”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 34
},{
“_id”: “-1840819204”,
“comments”: “mongoupload”,
“date”: “2019-06-01”,
“site”: “G”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 38
},{
“_id”: “-1840074429”,
“comments”: “mongoupload”,
“date”: “2019-10-01”,
“site”: “G”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 34
},{
“_id”: “-1840968159”,
“comments”: “mongoupload”,
“date”: “2019-01-01”,
“site”: “G”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 42
},{
“_id”: “-1840878786”,
“comments”: “mongoupload”,
“date”: “2019-04-01”,
“site”: “G”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 39
},{
“_id”: “-1840938368”,
“comments”: “mongoupload”,
“date”: “2019-02-01”,
“site”: “G”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 42
},{
“_id”: “-1840789413”,
“comments”: “mongoupload”,
“date”: “2019-07-01”,
“site”: “G”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 38
},{
“_id”: “-1840759622”,
“comments”: “mongoupload”,
“date”: “2019-08-01”,
“site”: “G”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 41
},{
“_id”: “-1840848995”,
“comments”: “mongoupload”,
“date”: “2019-05-01”,
“site”: “G”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 38
},{
“_id”: “-1840729831”,
“comments”: “mongoupload”,
“date”: “2019-09-01”,
“site”: “G”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 39
},{
“_id”: “-1840014847”,
“comments”: “mongoupload”,
“date”: “2019-12-01”,
“site”: “G”,
“country”: “”,
“pdg”: “PD”,
“type”: “type1”,
“resources”: 31
}]

Hello @Sam_Sam, welcome to the MogoDB community forum.

Please post a sample input document (properly formatted). Also, specify the MongoDB, Java Driver and the Spring Data MongoDB versions.


[ EDIT ADD ]

org.springframework.data.mongodb.repository.Aggregation annotation can be used for aggregation queries for the repository query methods - this is similar to @Query used for JSON based queries. Here is an example post:

To run an aggregation group query using the Spring repository object, you have to use the @Aggregation instead of the @Query

I updated the post with the document format in JSON.
I tried @aggregation but it gives me this error
org.bson.BsonInvalidOperationException: readStartDocument can only be called when CurrentBSONType is DOCUMENT, not when CurrentBSONType is ARRAY.
I removed the array brackets and it worked but in this case it only returned the results from thefirst match in the pipelline . I also tried setting the pipeline value but still got the same results.

I tried this in a similar Java and Spring setup as yours:

Input collection documents:

{
        "_id" : "-1693282609",
        "comments" : "mongoupload",
        "date" : "2019-11-01",
        "site" : "T",
        "country" : "",
        "pdg" : "PD",
        "type" : "type1",
        "resources" : 1
}
{
        "_id" : "-1694086966",
        "comments" : "mongoupload",
        "date" : "2019-05-01",
        "site" : "T",
        "country" : "",
        "pdg" : "PD",
        "type" : "type1",
        "resources" : 2
}

Spring Data MongoDB code (same Java code from your posting):

	MongoCollection<Document> coll = mongoTemplate.getCollection("sample");

	List<Bson> pipeline = Arrays.asList(
            match(and(eq("pdg", "PD"), eq("type", "type1"),
            and(gte("date", "2019-01-01"), lte("date", "2019-12-01")))),
            sort(orderBy(ascending("pdg"), ascending("date"))),
            group(eq("site", "$site"),
                    push("wf", and(eq("site", "$site"), eq("date", "$date"), eq("resources", "$resources"))),
                    sum("tresources", "$resources")),
            match(gt("tresources", 0L)));
	
	List<Document> result = coll.aggregate(pipeline).into(new ArrayList<Document>());
	result.forEach(doc -> System.out.println(doc.toJson()));;

I get the same result from the aggregation run from the mongo shell query and the Spring Java code.

{
   "_id":{
      "site":"T"
   },
   "wf":[
      {
         "site":"T",
         "date":"2019-05-01",
         "resources":2.0
      },
      {
         "site":"T",
         "date":"2019-11-01",
         "resources":1.0
      }
   ],
   "tresources":3.0
}

Let me know if you are getting the same results, else what is it you are expecting.

[ EDIT ADD ]: I have a correction in this post.

The above output is from the MongoDB Java Driver code , not from the Spring Java code. The output from Spring Java code is (as you had posted, different):

{
   "_id":{
      "site":"T"
   },
   "wf":[
      true,
      true
   ],
   "tresources":3.0
}

Here is a solution (I think).

This uses the MongoTemplate#executeCommand method - “Execute a MongoDB command expressed as a JSON string”. This is equivalent to MongoDB shell’s database command which is run as db.runCommand( { <command> } ). The command in this case is the aggregate database aggregation command which has the following syntax:

{
  aggregate: "<collection>" ,
  pipeline: [ <stage>, <...> ],
  ...,
  cursor: <document>,
  ...
}

Note the three are mandatory arguments (others omitted for brevity, and are not used in this case).

Now, build the pipeline from the aggregation and run the command using the Spring Data’s MongoTemplate API using the data from my previous post.

String match1 = "{ '$match':{ 'pdg':'PD', 'type':'type1', 'date':{ '$gte':'2019-01-01', '$lte':'2019-12-01' } } }";
String sort = "{ '$sort':{ 'pdg':1, 'date':1 } }";
String group = "{ '$group':{ '_id':{ 'site':'$site' }, 'wf':{ '$push':{  'date':'$date', 'resources': '$resources', 'tt':'$n' } }, 'tresources':{ '$sum':'$resources' } } }";
String match2 = "{ '$match':{ 'tresources':{ '$gt':0 } } }";

String pipe = match1 + ", " + sort + ", " + group + ", " +  match2;
String cmd = "{ 'aggregate': 'sample', 'pipeline': [" + pipe + "], 'cursor': { } }";

MongoTemplate template = new MongoTemplate(MongoClients.create(), "test");
Document result = template.executeCommand(cmd);
System.out.println(result.toJson());

Here is a solution using the MongoRepository API (originally you are looking for):

Repository with the aggregation method:

public interface SampleRepository extends MongoRepository<Sample, Integer> {

    public static final String match1 = "{ '$match':{ 'pdg': ?0, 'type': ?1, 'date':{ '$gte': ?2, '$lte': ?3 } } }";
    public static final String sort = "{ '$sort':{ 'pdg':1, 'date':1 } }";
    public static final String group = "{ '$group':{ '_id':{ 'site':'$site' }, 'wf':{ '$push':{  'date':'$date', 'resources': '$resources', 'site':'$site' } }, 'tresources':{ '$sum':'$resources' } } }";
    public static final String match2 = "{ '$match':{ 'tresources':{ '$gt':0 } } }";
	
    @Aggregation(pipeline = { match1, sort, group, match2 })
    List<PojoOut> aggregateBySample(String pdg, String type, String date1, String date2);
}

Running the aggregation:

List<PojoOut> list = sampleRepository.aggregateBySample("PD", "type1", "2019-01-01", "2019-12-01");

PojoOut class is of the output type of this document:

{
        "_id" : {
                "site" : "T"
        },
        "wf" : [
                {
                        "site" : "T",
                        "date" : "2019-05-01",
                        "resources" : 2
                },
                {
                        "site" : "T",
                        "date" : "2019-11-01",
                        "resources" : 1
                }
        ],
        "tresources" : 3,
        "_class" : "com.example.demo.PojoOut"
}

I am actually migrating from arango to mongo . I did the query in arango web client and pasted the query with argument parameters in @query the results were the same.
It seemed that I can skip the mongo repository and get results using Mongo template but I don’t know why the results are not the same .
My issue is that the output in Spring is different from the output in Compass . And you are also getting the same results , I guess there is a bug in Sping , But can you share how did you use the Java driver for that query.

Thanks this solution works for me for now.

I had already posted the code in an earlier post. It is possible it is bug (but, I don’t know). See the one with:

I tried this in a similar Java and Spring setup as yours:

Input collection documents: …

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.