Reputation: 5660
{
"customerSchemes": [
{
"name": "A",
"startDate": some date in valid date format
},
{
"name": "B",
"startDate": some date in valid date format.
}
]
}
I am trying to figure out all documents where scheme A started before scheme B. Please note that the scheme Array is not in ascending order of startDate. Plan B can have an earlier date as compared to plan A. I believe unwind operator could be of some use here but not sure how to progress with next steps.
Upvotes: 5
Views: 3365
Reputation: 4452
So the idea is
customerSchemes
array by startDate
.customerSchemes.name
is A
.Try this query:
db.collection.aggregate([
{ $unwind: "$customerSchemes" },
{
$sort: { "customerSchemes.startDate": 1 }
},
{
$group: {
_id: "$_id",
customerSchemes: { $push: "$customerSchemes" }
}
},
{
$match: {
$expr: {
$eq: [{ $first: "$customerSchemes.name" }, "A"]
}
}
}
]);
Output:
/* 1 createdAt:3/12/2021, 6:40:42 PM*/
{
"_id" : ObjectId("604b685232a8d433d8ede6c4"),
"customerSchemes" : [
{
"name" : "A",
"startDate" : ISODate("2021-03-01T00:00:00.000+05:30")
},
{
"name" : "B",
"startDate" : ISODate("2021-03-02T00:00:00.000+05:30")
}
]
},
/* 2 createdAt:3/12/2021, 6:40:42 PM*/
{
"_id" : ObjectId("604b685232a8d433d8ede6c6"),
"customerSchemes" : [
{
"name" : "A",
"startDate" : ISODate("2021-03-01T00:00:00.000+05:30")
},
{
"name" : "B",
"startDate" : ISODate("2021-03-05T00:00:00.000+05:30")
}
]
}
Test data:
/* 1 createdAt:3/12/2021, 6:40:42 PM*/
{
"_id" : ObjectId("604b685232a8d433d8ede6c4"),
"customerSchemes" : [
{
"name" : "A",
"startDate" : ISODate("2021-03-01T00:00:00.000+05:30")
},
{
"name" : "B",
"startDate" : ISODate("2021-03-02T00:00:00.000+05:30")
}
]
},
/* 2 createdAt:3/12/2021, 6:40:42 PM*/
{
"_id" : ObjectId("604b685232a8d433d8ede6c5"),
"customerSchemes" : [
{
"name" : "A",
"startDate" : ISODate("2021-03-03T00:00:00.000+05:30")
},
{
"name" : "B",
"startDate" : ISODate("2021-03-02T00:00:00.000+05:30")
}
]
},
/* 3 createdAt:3/12/2021, 6:40:42 PM*/
{
"_id" : ObjectId("604b685232a8d433d8ede6c6"),
"customerSchemes" : [
{
"name" : "B",
"startDate" : ISODate("2021-03-05T00:00:00.000+05:30")
},
{
"name" : "A",
"startDate" : ISODate("2021-03-01T00:00:00.000+05:30")
}
]
}
Upvotes: 2
Reputation: 75984
You could use $unwind
array and format the elements for comparison effectively transforming into key value pair. This assumes you only have two array values so I didn't know apply any filtering.
Something like
db.colname.aggregate(
[
{"$unwind":"$customerSchemes"},
{"$group":{
"_id":"$_id",
"data":{"$push":"$$ROOT"},
"fields":{
"$mergeObjects":{
"$arrayToObject":[[["$customerSchemes.name","$customerSchemes.startDate"]]]
}
}
}},
{"$match":{"$expr":{"$lt":["$fields.A","$fields.B"]}}},
{"$project":{"_id":0,"data":1}}
])
Working example here - https://mongoplayground.net/p/mSmAXHm0-o-
Using $reduce
db.colname.aggregate(
[
{"$addFields":{
"fields":{
"$reduce":{
"input":"$customerSchemes",
"initialValue":{},
"in":{
"$mergeObjects":[
{"$arrayToObject":[[["$$this.name","$$this.startDate"]]]},
"$$value"]
}
}
}
}},
{"$match":{"$expr":{"$lt":["$fields.A","$fields.B"]}}},
{"$project":{"fields":0}}
])
Working example here - https://mongoplayground.net/p/WNxbScI9N9b
Upvotes: 4
Reputation: 36144
$filter
to filter name: "A"
from customerSchemes
$arrayElemAt
to get first element from filtered result from above stepname: "B"
$let
to declare variables for "A" in a
and "B" in b
in
to check condition from above variables if a
's startDate
is greater than b
's startDate
then return true otherwise false$expr
expression match with $eq
to match above process, if its true then return documentdb.collection.aggregate([
{
$match: {
$expr: {
$eq: [
{
$let: {
vars: {
a: {
$arrayElemAt: [
{
$filter: {
input: "$customerSchemes",
cond: { $eq: ["$$this.name", "A"] }
}
},
0
]
},
b: {
$arrayElemAt: [
{
$filter: {
input: "$customerSchemes",
cond: { $eq: ["$$this.name", "B" ] }
}
},
0
]
}
},
in: { $gt: ["$$a.startDate", "$$b.startDate"] }
}
},
true
]
}
}
}
])
You can use above match stage expression condition in find()
query as well without any aggregation pipeline,
latest support hint: if you are using latest(4.4) MongoDB version then you can use
$first
instead of$arrayElemAt
, see Playground
Upvotes: 4