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
}]
…
’