Pull out specific elements based on if a field exists or not in aggregate pipeline - Javascript

Amaarockz
April 12, 2023
149 views
1 vote
2 Answers

This is a followup question to Draft an aggregate to group based on a nested attribute with lookup
There are these two collections as listed below

orders: [
  {
    "_id": "64355c928dcce8cdf4b9c7d2",
    "destinations": [
      {
        "ship_to_id": "64355c92af10d37993473e12", // mongoose id from locations collection
        "sold_to_id": "64355c92a57d8b29412a1cc2" // mongoose id from locations collection
        "type" : 1,
      },
      {
        "ship_to_id": "64355c92a57d8b29412a1cc2",
        "sold_to_id": "64355c92ed8af3f7cd7199b2"
      },
      {
        "ship_to_id": "64355c92256aa652e6c3fdc5",
        "sold_to_id": "64355c924f9a2fcafa90daed"
      }
    ],
    "contact_details": [
      {
        "ship_to_ref": "aaa",
        "sold_to_ref": "bbb",
        "contact_email": "[email protected]",
      },
      {
        "ship_to_ref": "bbb",
        "sold_to_ref": "ccc",
        "contact_email": "[email protected]"
      },
      {
        "ship_to_ref": "ddd",
        "sold_to_ref": "eee",
        "contact_email": "[email protected]",
      }
    ]
  },
  {
    "_id": "64355c92bf25e54cf901be39",
    "destinations": [
      {
        "ship_to_id": "64355c92af10d37993473e12",
        "sold_to_id": "64355c92a57d8b29412a1cc2",
      },
      {
        "ship_to_id": "64355c92a57d8b29412a1cc2",
        "sold_to_id": "64355c92ed8af3f7cd7199b2",
        "type": 2,
      },
      {
        "ship_to_id": "64355c92af10d37993473e12",
        "sold_to_id": "64355c92256aa652e6c3fdc5",
        "type": null,
      }
    ],
    "contact_details": [
      {
        "ship_to_ref": "aaa",
        "sold_to_ref": "bbb",
        "contact_email": "[email protected]",
      },
      {
        "ship_to_ref": "bbb",
        "sold_to_ref": "ccc",
        "contact_email": "[email protected]",
      },
      {
        "ship_to_ref": "aaa",
        "sold_to_ref": "ddd",
        "contact_email": "[email protected]",
      }
    ]
  },
  {
    "_id": "64355c921445785f4b50040e",
    "destinations": [
      {
        "ship_to_id": "64355c92af10d37993473e12",
        "sold_to_id": "64355c92a57d8b29412a1cc2"
      },
      {
        "ship_to_id": "64355c92af10d37993473e12",
        "sold_to_id": "64355c92ed8af3f7cd7199b2"
      }
    ],
    "contact_details": [
      {
        "ship_to_ref": "aaa",
        "sold_to_ref": "bbb",
        "contact_email": "[email protected]",
      },
      {
        "ship_to_ref": "aaa",
        "sold_to_ref": "ccc",
        "contact_email": "[email protected]",
      },
    ]
  }
]

and

locations: [
  {
    "_id": "64355c92af10d37993473e12",
    "reference_id": "aaa"
  },
  {
    "_id": "64355c92a57d8b29412a1cc2",
    "reference_id": "bbb"
  },
  {
    "_id": "64355c92ed8af3f7cd7199b2",
    "reference_id": "ccc"
  },
  {
    "_id": "64355c92256aa652e6c3fdc5",
    "reference_id": "ddd"
  },
  {
    "_id": "64355c924f9a2fcafa90daed",
    "reference_id": "eee"
  },
]

Now what I want to achieve is write an aggregate query that groups me all the indents based on same ship_to, sold_to and contact_email (Note: to verify if thats a valid ship_to and sold_to we need to lookup from the locations collection using the ship_to_id and sold_to_id, retrieve the respective reference_id 's and then compare it with the ship_to_ref and sold_to_ref that is available in contact details) such that the query result will be like

[{
  _id: {
    ship_to_ref: "aaa",
    sold_to_ref: "bbb",
    ship_to_id:"64355c92af10d37993473e12"
    sold_to_id:"64355c92a57d8b29412a1cc2"
    contact_email: "[email protected]"
   },
   orders: [
     { "_id": "64355c928dcce8cdf4b9c7d2", ... },
     { "_id": "64355c92bf25e54cf901be39", ... },
     { "_id" : "64355c921445785f4b50040e", ...}
   ],
 },
 {
  _id: {
    ship_to_ref: "bbb",
    sold_to_ref: "ccc",
    ship_to_id:"64355c92a57d8b29412a1cc2",
    sold_to_id:"64355c92ed8af3f7cd7199b2",
    contact_email: "[email protected]"
   },
   orders: [
     { "_id": "64355c928dcce8cdf4b9c7d2", ... },
     { "_id": "64355c92bf25e54cf901be39", ... },
   ],
 },
 {
  _id: {
    ship_to_ref: "ddd",
    sold_to_ref: "eee",
    ship_to_id: "64355c92256aa652e6c3fdc5",
    sold_to_id: "64355c924f9a2fcafa90daed",
    "contact_email": "[email protected]",
   },
   orders: [
     { "_id": "64355c928dcce8cdf4b9c7d2", ... }
   ],
 },
{
  _id: {
    ship_to_ref: "aaa",
    sold_to_ref: "ddd",
    ship_to_id: "64355c92af10d37993473e12",
    sold_to_id: "64355c92256aa652e6c3fdc5",
    contact_email: "[email protected]",
   },
   orders: [
     { "_id": "64355c92bf25e54cf901be39", ... }
   ],
 },
{
  _id: {
    ship_to_ref: "aaa",
    sold_to_ref: "ccc",
    ship_to_id: "64355c92af10d37993473e12",
    sold_to_id: "64355c92ed8af3f7cd7199b2",
    contact_email: "[email protected]",
   },
   orders: [
     { "_id": "64355c921445785f4b50040e", ... }
   ],
 },
]

Below is an aggregate query that is working as expected

db.orders.aggregate([
  {$match: {contact_details: {$exists: true}}},
  {$project: {
      del: {$map: {
          input: {$range: [0, {$size: "$contact_details"}]},
          in: {
            destination: {$arrayElemAt: ["$destinations", "$$this"]},
            contact: {$arrayElemAt: ["$contact_details", "$$this"]},
            destinations: "$destinations"
          }
      }}
  }},
  {$unwind: "$del"},
  {$group: {
      _id: {
        sold_to_id: "$del.destination.sold_to_id",
        ship_to_id: "$del.destination.ship_to_id"
      },
      contact_email: {$first: "$del.contact.contact_email"},
      orders: {$push: {
          _id: "$_id", 
          contact_details: "$del.contact",
          destinations: "$del.destinations"
      }}
  }},
  {$set: {locations: ["$_id.sold_to_id", "$_id.ship_to_id"]}},
  {$lookup: {
      from: "locations",
      localField: "locations",
      foreignField: "_id",
      as: "locations"
  }},
  {$project: {
      "_id.ship_to_id": "$_id.ship_to_id",
      "_id.sold_to_id": "$_id.sold_to_id",
      "_id.contact_email": "$contact_email",
      "_id.ship_to_ref": {$getField: {
          input: {$arrayElemAt: [
              "$locations",
              {$indexOfArray: ["$locations._id", "$_id.ship_to_id"]}
          ]},
          field: "reference_id"
      }},
      "_id.sold_to_ref": {$getField: {
          input: {$arrayElemAt: [
              "$locations",
              {$indexOfArray: ["$locations._id", "$_id.sold_to_id"]}
          ]},
          field: "reference_id"
      }},
      orders: 1
  }}
])

As you can see in the above query, I am using project and iterating through all the existing destinations. Now the requirement is that to filter out the destinations in which the attribute type doesn’t exist or type = null.
I guess I need to use $reduce instead of $map but not sure how to get that working.

Thanks in advance

Answers

Chosen as BEST ANSWER

Below is the new query that worked

db.indents.aggregate([
  {
    $project: {
      _id: 1,
      destinations: 1,
      contact_details: 1,
      contacts_gt_1: {
        $gt: [
          {
            $size: { 
             $cond: [ 
                { $isArray: "$contact_details" }, 
                "$contact_details", 
                []
             ]
            } 
          },
          0
        ]
      }
    }
  },
  {
    $match: {
      contacts_gt_1: true,
    }
  },
  {
    $project: {
      _id: 1,
      destinations: 1,
      contact_details: 1,
    }
  },
  {
    $unwind: "$destinations"
  },
  {
    $lookup: {
      from: "customers",
      localField: "destinations.sold_to_id",
      foreignField: "_id",
      as: "sold_to_ref"
    },
  },
  {
    $unwind: "$sold_to_ref"
  },
  {
    $lookup: {
      from: "customers",
      localField: "destinations.ship_to_id",
      foreignField: "_id",
      as: "ship_to_ref"
    }
  },
  {
    $unwind: "$ship_to_ref"
  },
  {
    $project: {
      _id: 1,
      destinations: 1,
      contact_details: 1,
      ship_to_ref: "$ship_to_ref.reference_id",
      sold_to_ref: "$sold_to_ref.reference_id",
    }
  },
  {
    $unwind: "$contact_details"
  },
  {
    $group: {
      _id: {
        ship_to_ref: "$contact_details.ship_to_ref",
        sold_to_ref: "$contact_details.sold_to_ref",
        contact_email: "$contact_details.contact_email",
      },
      orderData: {
        $addToSet: {
          _id: "$_id",
          destinations: "$destinations",  
        },
      },
    },
  },
  {
    $unwind: "$orderData"
  },
  {
    $group: {
      _id: {
        order_id: "$orderData._id",
        entry: "$_id",
      },
      destinations: {
        $push: "$orderData.destinations"
      },  
    }
  },
  {
    $group: {
      _id: "$_id.entry",
      orders: {
        $push: {
          order_id: "$_id.order_id",
          destinations: "$destinations"
        }
      }
    }
  },
])

(Edit)

One option is to add another step to $filter before the $unwind:

db.orders.aggregate([
  {$match: {contact_details: {$exists: true}}},
  {$project: {
      del: {$filter: {
          input: "$destinations",
          cond: {$gte: ["$$this.type", 0]}
      }},
      destinations: 1,
      contact_details: 1
  }},
  {$unwind: "$del"},
  {$group: {
      _id: {
        sold_to_id: "$del.sold_to_id",
        ship_to_id: "$del.ship_to_id"
      },
      contact_details: {$first: "$contact_details"},
      orders: {$push: {
          _id: "$_id",
          destinations: "$destinations"
      }}
  }},
  {$set: {locations: ["$_id.sold_to_id", "$_id.ship_to_id"]}},
  {$lookup: {
      from: "locations",
      localField: "locations",
      foreignField: "_id",
      as: "locations"
  }},
  {$project: {
      contact_details: 1,
      "_id.ship_to_id": "$_id.ship_to_id",
      "_id.sold_to_id": "$_id.sold_to_id",
      "_id.contact_email": "$contact_email",
      "_id.ship_to_ref": {$getField: {
          input: {$arrayElemAt: [
              "$locations",
              {$indexOfArray: ["$locations._id", "$_id.ship_to_id"]}
          ]},
          field: "reference_id"
      }},
      "_id.sold_to_ref": {$getField: {
          input: {$arrayElemAt: [
              "$locations",
              {$indexOfArray: ["$locations._id", "$_id.sold_to_id"]}
          ]},
          field: "reference_id"
      }},
      orders: 1
  }},
  {$set: {
      contact_details: "$$REMOVE",
      "_id.contact_email": {$getField: {
          input: {
            $first: {$filter: {
                input: "$contact_details",
                cond: {$and: [
                    {$eq: ["$$this.ship_to_ref", "$_id.ship_to_ref"]},
                    {$eq: ["$$this.sold_to_ref", "$_id.sold_to_ref"]}
                ]}
            }}
          },
          field: "contact_email"
      }}
  }}
])

See how it works on the playground example

Please signup or login to give your own answer.

Click here to cancel reply.

Pull out specific elements based on if a field exists or not in aggregate pipeline – Javascript

Answers