How would I convert the following MongoDB query into a query to be used by my Java Spring application? I can't find a way to use pipeline with the provided lookup method.
Here is the query I am attempting to convert. I also want to note that I didn't use $unwind as I wanted the deliveryZipCodeTimings to stay as a grouped collection in the return object.
db.getCollection('fulfillmentChannel').aggregate([
{
$match: {
"dayOfWeek": "SOME_VARIABLE_STRING_1"
}
},
{
$lookup: {
from: "deliveryZipCodeTiming",
let: { location_id: "$fulfillmentLocationId" },
pipeline: [{
$match: {
$expr: {
$and: [
{$eq: ["$fulfillmentLocationId", "$$location_id"]},
{$eq: ["$zipCode", "SOME_VARIABLE_STRING_2"]}
]
}
}
},
{
$project: { _id: 0, zipCode: 1, cutoffTime: 1 }
}],
as: "deliveryZipCodeTimings"
}
},
{
$match: {
"deliveryZipCodeTimings": {$ne: []}
}
}
])
Always Learning :
Building upon the info given by @dnickless, I was able to solve this. I'll post the complete solution in the hopes it helps someone else in the future.\n\nI'm using mongodb-driver:3.6.4\n\nFirst, I had to create a custom aggregation operation class so that I could pass in a custom JSON mongodb query to be used in the aggregation operation. This will allow me to use pipeline within a $lookup which is not supported with the driver version I am using.\n\npublic class CustomProjectAggregationOperation implements AggregationOperation {\n private String jsonOperation;\n\n public CustomProjectAggregationOperation(String jsonOperation) {\n this.jsonOperation = jsonOperation;\n }\n\n @Override\n public Document toDocument(AggregationOperationContext aggregationOperationContext) {\n return aggregationOperationContext.getMappedObject(Document.parse(jsonOperation));\n }\n}\n\n\nNow that we have the ability to pass a custom JSON query into our mongodb spring implementation, all that is left is to plug those values into a TypedAggregation query.\n\npublic List<FulfillmentChannel> getFulfillmentChannels(\n String SOME_VARIABLE_STRING_1, \n String SOME_VARIABLE_STRING_2) {\n\n AggregationOperation match = Aggregation.match(\n Criteria.where(\"dayOfWeek\").is(SOME_VARIABLE_STRING_1));\n AggregationOperation match2 = Aggregation.match(\n Criteria.where(\"deliveryZipCodeTimings\").ne(Collections.EMPTY_LIST));\n String query =\n \"{ $lookup: { \" +\n \"from: 'deliveryZipCodeTiming',\" +\n \"let: { location_id: '$fulfillmentLocationId' },\" +\n \"pipeline: [{\" +\n \"$match: {$expr: {$and: [\" +\n \"{ $eq: ['$fulfillmentLocationId', '$$location_id']},\" +\n \"{ $eq: ['$zipCode', '\" + SOME_VARIABLE_STRING_2 + \"']}]}}},\" +\n \"{ $project: { _id: 0, zipCode: 1, cutoffTime: 1 } }],\" +\n \"as: 'deliveryZipCodeTimings'}}\";\n\n TypedAggregation<FulfillmentChannel> aggregation = Aggregation.newAggregation(\n FulfillmentChannel.class,\n match,\n new CustomProjectAggregationOperation(query),\n match2\n );\n\n AggregationResults<FulfillmentChannel> results = \n mongoTemplate.aggregate(aggregation, FulfillmentChannel.class);\n return results.getMappedResults();\n}\n",
2018-07-05T21:13:02
dma_k :
I would like to add this my solution which is repeating in some aspect the solutions posted before.\nMongo driver v3.x\nFor Mongo driver v3.x I came to the following solution:\nimport java.util.Collections;\nimport java.util.List;\nimport java.util.Map;\nimport java.util.stream.Collectors;\n\nimport com.mongodb.BasicDBList;\nimport com.mongodb.BasicDBObject;\nimport com.mongodb.util.JSON;\n\nimport org.bson.Document;\nimport org.springframework.data.mongodb.core.aggregation.AggregationOperation;\nimport org.springframework.data.mongodb.core.aggregation.AggregationOperationContext;\n\npublic class JsonOperation implements AggregationOperation {\n\n private List<Document> documents;\n\n public JsonOperation(String json) {\n Object root = JSON.parse(json);\n\n documents = root instanceof BasicDBObject\n ? Collections.singletonList(new Document(((BasicDBObject) root).toMap()))\n : ((BasicDBList) root).stream().map(item -> new Document((Map<String, Object>) ((BasicDBObject) item).toMap())).collect(Collectors.toList());\n }\n\n @Override\n public Document toDocument(AggregationOperationContext context) {\n // Not necessary to return anything as we override toPipelineStages():\n return null;\n }\n\n @Override\n public List<Document> toPipelineStages(AggregationOperationContext context) {\n return documents;\n }\n}\n\nand then provided that aggregation steps are given in some resource aggregations.json:\n[\n {\n $match: {\n "userId": "..."\n }\n },\n {\n $lookup: {\n let: {\n ...\n },\n from: "another_collection",\n pipeline: [\n ...\n ],\n as: "things"\n }\n },\n {\n $sort: {\n "date": 1\n }\n }\n]\n\none can use above class as follows:\nimport static org.springframework.data.mongodb.core.aggregation.Aggregation.newAggregation;\n\nCollection<ResultDao> results = mongoTemplate.aggregate(newAggregation(new JsonOperation(resourceToString("aggregations.json", StandardCharsets.UTF_8))), "some_collection", ResultDao.class).getMappedResults();\n\nMongo driver v4.x\nAs JSON class was removed from Mongo v4, I have rewritten the class as follows:\nimport java.util.Collections;\nimport java.util.List;\n\nimport org.bson.Document;\nimport org.springframework.data.mongodb.core.aggregation.AggregationOperation;\nimport org.springframework.data.mongodb.core.aggregation.AggregationOperationContext;\n\npublic class JsonOperation implements AggregationOperation {\n\n private List<Document> documents;\n\n private static final String DUMMY_KEY = "dummy";\n\n public JsonOperation(String json) {\n documents = parseJson(json);\n }\n\n static final List<Document> parseJson(String json) {\n return (json.startsWith("["))\n ? Document.parse("{\\"" + DUMMY_KEY + "\\": " + json + "}").getList(DUMMY_KEY, Document.class)\n : Collections.singletonList(Document.parse(json));\n }\n\n @Override\n public Document toDocument(AggregationOperationContext context) {\n // Not necessary to return anything as we override toPipelineStages():\n return null;\n }\n\n @Override\n public List<Document> toPipelineStages(AggregationOperationContext context) {\n return documents;\n }\n\n @Override\n public String getOperator() {\n return documents.iterator().next().keySet().iterator().next();\n }\n}\n\nbut implementation is now a bit ugly because of string manipulations. If somebody has a better idea of how to parse array of objects in a more elegant way, please edit this post or drop a comment. Ideally there should be some method in Mongo core that allows to parse either JSON object or list (returns BasicDBObject/BasicDBList or Document/List<Document>).\nAlso note that I have skipped the step of transforming Document instances in toPipelineStages() method as it is not necessary in my case:\n@Override\npublic List<Document> toPipelineStages(AggregationOperationContext context) {\n return documents.stream().map(document -> context.getMappedObject(document)).collect(Collectors.toList());\n}\n\n",
2020-08-28T17:16:27
dnickless :
The drivers are pretty much always a little bit behind the current language features that MongoDB provides - hence some of the latest and greatest features are simply not nicely accessible through the API yet. I am afraid this is one of those cases and you'll need to resort to using strings. Kind of like so (untested):\n\nAggregationOperation match = Aggregation.match(Criteria.where(\"dayOfWeek\").is(\"SOME_VARIABLE_STRING_1\"));\nAggregationOperation match2 = Aggregation.match(Criteria.where(\"deliveryZipCodeTimings\").ne([]));\nString query = \"{ $lookup: { from: 'deliveryZipCodeTiming', let: { location_id: '$fulfillmentLocationId' }, pipeline: [{ $match: { $expr: { $and: [ { $eq: ['$fulfillmentLocationId', '$$location_id']}, { $eq: ['$zipCode', 'SOME_VARIABLE_STRING_2']} ]} } }, { $project: { _id: 0, zipCode: 1, cutoffTime: 1 } }], as: 'deliveryZipCodeTimings' } }\";\nAggregation.newAggregation(match, (DBObject) JSON.parse(query), match2);\n",
2018-06-29T20:48:45
mramsath :
I faced some JSON parsing exceptions when I used the way explained in the accepted answer, so I dig deep the default MongoDB java driver(version 3) Document class to build up aggregation query and found out any aggregation query can be build u as follows,\nReplace each of the element in the mongo console query as follows\n\nCurly braces({) -> new Document()\nparameter names are same\nColon(:) -> Coma(,)\nComa(,) -> .append()\nSquare bracket([) -> Arrays.asList()\n\n AggregationOperation customLookupOperation = new AggregationOperation() {\n @Override\n public Document toDocument(AggregationOperationContext context) {\n return new Document(\n "$lookup",\n new Document("from", "deliveryZipCodeTiming")\n .append("let",new Document("location_id", "$fulfillmentLocationId"))\n .append("pipeline", Arrays.<Object> asList(\n new Document("$match", new Document("$expr", new Document("$and",\n Arrays.<Object>asList(\n new Document("$eq", Arrays.<Object>asList("$fulfillmentLocationId", "$$location_id")),\n new Document("$eq", Arrays.<Object>asList("$zipCode", "SOME_VARIABLE_STRING_2"))\n )))),\n new Document("$project", new Document("_id",0).append("zipCode", 1)\n .append("cutoffTime", 1)\n)\n ))\n .append("as", "deliveryZipCodeTimings")\n );\n }\n };\n\nFinally you can use the aggregation operation in the aggrgation pipeline,\n Aggregation aggregation = Aggregation.newAggregation(matchOperation,customLookupOperation,matchOperation2);\n",
2021-07-06T12:53:06