We would like to filter SKU's List which has verificationData data and differenceInStock difference greater than or Less than 0
Here is an example Data Set.
[
{
"_id": "636e0beaa13ef73324e613f0",
"status": "ACTIVE",
"inventory": 132,
"parentCategory": [
"Salt"
],
"title": "Aashirvaad MRP: 28Rs Salt 27 kg Bopp Bag (Set of 1 kg x 27)",
"createdAt": "2022-11-11T08:46:34.950Z",
"updatedAt": "2022-11-24T17:43:27.361Z",
"__v": 3,
"verificationData": [
{
"_id": "637c57ebbe783a9a138fc2d3",
"verificationDate": "2022-11-22T05:02:35.155Z",
"items": {
"listingId": "636e0beaa13ef73324e613f0",
"phyiscalVerification": [
{
"verifiedBy": "634534e72ef6462fcb681a39",
"closingStock": 178,
"phyiscalStock": 178,
"differenceInStock": 0,
"verifiedAt": "2022-11-22T10:19:38.388Z",
"_id": "637ca23abe783a9a1394f402"
}
],
"_id": "637ca23abe783a9a1394f401"
},
"yearMonthDayUTC": "2022-11-22"
},
{
"_id": "637d9b65be783a9a13998726",
"verificationDate": "2022-11-23T04:02:45.804Z",
"items": {
"listingId": "636e0beaa13ef73324e613f0",
"phyiscalVerification": [
{
"verifiedBy": "634534e72ef6462fcb681a39",
"closingStock": 161,
"phyiscalStock": 167,
"differenceInStock": 6,
"verifiedAt": "2022-11-23T09:52:36.815Z",
"_id": "637ded64be783a9a13a29d55"
}
],
"_id": "637ded64be783a9a13a29d54"
},
"yearMonthDayUTC": "2022-11-23"
},
{
"_id": "637f0254be783a9a13a94354",
"verificationDate": "2022-11-24T05:34:12.995Z",
"items": {
"listingId": "636e0beaa13ef73324e613f0",
"phyiscalVerification": [
{
"verifiedBy": "634534e72ef6462fcb681a39",
"closingStock": 144,
"phyiscalStock": 146,
"differenceInStock": 2,
"verifiedAt": "2022-11-24T12:02:28.123Z",
"_id": "637f5d54be783a9a13b1039a"
}
],
"_id": "637f5d54be783a9a13b10399"
},
"yearMonthDayUTC": "2022-11-24"
},
{
"_id": "2022-11-25",
"yearMonthDayUTC": "2022-11-25",
"items": null
}
]
},
{
"_id": "62b5c39062ddb963fc64c42d",
"status": "ACTIVE",
"inventory": 10,
"parentCategory": [
"Salt"
],
"finalMeasurementUnit": "kg",
"finalMeasure": "1 kg",
"title": "Marvella Citric Acid Lemon Salt 1 kg Pouch (Set of 500 gm x 2)",
"createdAt": "2022-06-24T14:00:49.052Z",
"updatedAt": "2022-11-21T11:04:21.643Z",
"__v": 2,
"verificationData": [
{
"_id": "2022-11-22",
"yearMonthDayUTC": "2022-11-22",
"items": null
},
{
"_id": "2022-11-23",
"yearMonthDayUTC": "2022-11-23",
"items": null
},
{
"_id": "2022-11-24",
"yearMonthDayUTC": "2022-11-24",
"items": null
},
{
"_id": "2022-11-25",
"yearMonthDayUTC": "2022-11-25",
"items": null
}
]
}
]
This could have array of 100 SKU's
Our Aggregate Functions is as Follows
let reqData = await userListing.aggregate([
{
$match: {
warehouseId: { $eq: ObjectId(warehouseId) },
parentCategory: { $in: catList },
isWarehouseListing: { $eq: true },
isBlocked: { $ne: true },
isArchived: { $ne: true },
},
},
{ $sort: { whAddedAt: -1 } },
{
$lookup: {
from: "listingstockverifications",
let: { listId: "$_id" },
pipeline: [
{
$match: {
verificationDate: {
$gte: newFromDate,
$lt: newToDate,
},
},
},
{
$project: {
verificationDate: 1,
items: {
$filter: {
input: "$items",
cond: {
$and: [
/* {
"$$this.phyiscalVerification": {
$filter: {
input: "$$this.phyiscalVerification",
as: "psitem",
cond: { $gt: [ "$$psitem.differenceInStock", 0 ] },
},
},
}, */
{
$eq: ["$$this.listingId", "$$listId"],
},
],
},
},
},
yearMonthDayUTC: {
$dateToString: {
format: "%Y-%m-%d",
date: "$verificationDate",
},
},
},
},
{ $unwind: "$items" },
],
as: "stockVerification",
},
},
{
$addFields: {
verificationData: {
$map: {
input: dummyArray,
as: "date",
in: {
$let: {
vars: {
dateIndex: {
$indexOfArray: [
"$stockVerification.yearMonthDayUTC",
"$$date",
],
},
},
in: {
$cond: {
if: { $ne: ["$$dateIndex", -1] },
then: {
$arrayElemAt: ["$stockVerification", "$$dateIndex"],
},
else: {
_id: "$$date",
yearMonthDayUTC: "$$date",
items: null,
},
},
},
},
},
},
},
},
},
{
$project: {
stockVerification: 0,
},
},
]);
At Last now we would like to filter the SKU List the which has following Data
verificationData[].items.phyiscalVerification[].differenceInStock is Greater than or Less than 0
Expected Output in the following Exmaple would be 1st SKUs as 2nd SKU does not have any Item Data and even if in 3rd SKU if we got Item Data but should match the following condition
verificationData[].items.phyiscalVerification[].differenceInStock is Greater than or Less than 0
Thank you for taking your time to read and support.
CodePudding user response：
You can add these two following stages to your aggregation, The idea is simple - just filter out all subdocuments that do not match the condition.
Because of the nested structure it's just not the sexiest of pipelines but it will suffice.
db.collection.aggregate([
{
$match: {
$or: [
{
"verificationData.items.phyiscalVerification.differenceInStock": {
$gt: 0
}
},
{
"verificationData.items.phyiscalVerification.differenceInStock": {
$lt: 0
}
}
]
}
},
{
$addFields: {
verificationData: {
$filter: {
input: {
$map: {
input: {
$filter: {
input: "$verificationData",
as: "verification",
cond: {
$ne: [
"$$verification.items",
null
]
}
}
},
as: "top",
in: {
$mergeObjects: [
"$$top",
{
"items": {
"$mergeObjects": [
"$$top.items",
{
phyiscalVerification: {
$filter: {
input: "$$top.items.phyiscalVerification",
as: "pshycical",
cond: {
$ne: [
"$$pshycical.differenceInStock",
0
]
}
}
}
}
]
}
}
]
}
}
},
cond: {
$gt: [
{
$size: "$$this.items.phyiscalVerification"
},
0
]
}
}
}
}
}
])
CodePudding user response：
To filter a list of SKUs in MongoDB based on the presence of a field and a numeric comparison, you can use the $exists operator to check for the presence of a field and the $gt (greater than) or $lt (less than) operators to compare numeric values. For example, you could use the following query to filter SKUs that have a verificationData field and a differenceInStock value that is greater than 0:
db.collection.find({
verificationData: { $exists: true },
"verificationData.items.phyiscalVerification.differenceInStock": { $gt: 0 }
})
This query will match all documents in the collection that have a verificationData field and a differenceInStock field within the phyiscalVerification array that is greater than 0. To match SKUs with a differenceInStock value that is less than 0, you can use the $lt operator instead:
db.collection.find({
verificationData: { $exists: true },
"verificationData.items.phyiscalVerification.differenceInStock": { $lt: 0 }
})
If you want to return only the specific fields specified in the query, you can use the projection argument of the find() method to specify which fields you want to include in the returned documents. For example:
// Query to return only the SKU and differenceInStock fields
db.skus.find(
{
verificationData: { $exists: true },
"verificationData.items.phyiscalVerification.differenceInStock": { $gt: 0 }
},
{
_id: 0,
SKU: 1,
"verificationData.items.phyiscalVerification.differenceInStock": 1
}
)
This will return only the SKU and differenceInStock fields for the documents that match the query conditions. You can adjust the projection to include or exclude other fields as needed.