| Steps To Reproduce: |
Insert in database these collections:
db.products.insert([
|
{ "_id" : ObjectId("5d605c5562348f7a74c3e86b"), "name" : "My name 1", "price" : 15, "key" : 3, "createdAt" : ISODate("2019-08-23T21:36:21.492Z"), "updatedAt" : ISODate("2019-08-30T16:55:15.536Z")},
|
{ "_id" : ObjectId("5d63b34f6c07b33ac413014a"), "name" : "My name 2", "price" : 14.90, "key" : 4, "createdAt" : ISODate("2019-08-26T10:24:15.701Z"), "updatedAt" : ISODate("2019-08-30T16:56:20.224Z")},
|
{ "_id" : ObjectId("5d63b3aa6c07b33ac413014c"), "name" : "My name 3", "price" : 14.90, "key" : 5, "createdAt" : ISODate("2019-08-26T10:25:46.238Z"), "updatedAt" : ISODate("2019-08-30T16:56:25.671Z")},
|
{ "_id" : ObjectId("5d6778d7a053172602d33b44"), "name" : "My name 4", "price" : 17, "key" : 6, "createdAt" : ISODate("2019-08-29T07:03:51.332Z"), "updatedAt" : ISODate("2019-08-29T07:03:51.332Z")}
|
])
|
db.productCharacteristics.insert([
|
{ "_id" : ObjectId("5d60624e1e09e27e89986562"), "active" : true, "productKey" : 3, "priority" : 0, "createdAt" : ISODate("2019-08-23T22:01:50.681Z"), "updatedAt" : ISODate("2019-08-30T16:55:15.537Z")},
|
{ "_id" : ObjectId("5d63b34f6c07b33ac413014b"), "active" : true, "productKey" : 4, "priority" : 0, "createdAt" : ISODate("2019-08-26T10:24:15.706Z"), "updatedAt" : ISODate("2019-08-30T16:56:20.224Z")},
|
{ "_id" : ObjectId("5d63b3aa6c07b33ac413014d"), "active" : true, "productKey" : 5, "priority" : 0, "createdAt" : ISODate("2019-08-26T10:25:46.239Z"), "updatedAt" : ISODate("2019-08-30T16:56:25.672Z")},
|
{ "_id" : ObjectId("5d6778d7a053172602d33b45"), "active" : true, "productKey" : 6, "priority" : 0, "createdAt" : ISODate("2019-08-29T07:03:51.339Z"), "updatedAt" : ISODate("2019-08-29T07:03:51.339Z")}
|
])
|
Then using this query
db.productCharacteristics.aggregate([
|
{ '$lookup': { from: 'products', localField: 'productKey', foreignField: 'key', as: 'data' } },
|
{ '$unwind': { path: '$data', preserveNullAndEmptyArrays: true } },
|
{ '$match': { 'data.price': { $gte: 12, $lte: 17 } } },
|
{ '$sort': { 'data.price': 1 } },
|
{ '$skip': 0 },
|
{ '$limit': 1 }
|
]).pretty()
|
Result is as expected
{{ "_id" : ObjectId("5d63b34f6c07b33ac413014b"), "active" : true, "productKey" : 4, "priority" : 0, "createdAt" : ISODate("2019-08-26T10:24:15.706Z"), "updatedAt" : ISODate("2019-08-30T16:56:20.224Z"), "data" : { "_id" : ObjectId("5d63b34f6c07b33ac413014a"), "name" : "My name 2", "price" : 14.9, "key" : 4, "createdAt" : ISODate("2019-08-26T10:24:15.701Z"), "updatedAt" : ISODate("2019-08-30T16:56:20.224Z") }}
|
then lets change '$skip': 0 to '$skip': 1 in the same query as above, then we got the same result. Which is totaly unexpected.
If we remove $skip and $limit, like this
db.productCharacteristics.aggregate([
|
{ '$lookup': { from: 'products', localField: 'productKey', foreignField: 'key', as: 'data' } },
|
{ '$unwind': { path: '$data', preserveNullAndEmptyArrays: true } },
|
{ '$match': { 'data.price': { $gte: 12, $lte: 17 } } },
|
{ '$sort': { 'data.price': 1 } }
|
]).pretty()
|
result is what we expect no duplicate items no unexpected behavior. The real problem is why when I skip the element I got the previous element
.
|