How to perform aggregation on multiple conditions on array of object in one collection

Working with Data Developer Tools
1

Hi ,
I have one problem while writing query. I want to perform lookup operation on two collection.
There are two collection user and customer.
user col:-

_id: ObejectId ("2325abcdef"),
description: Rahul Upraity,
address: xvyz
.
.

customer col :-

_id: ObjectId
name: abcd
type: 454
userLink :[
 userId : ObjectId ("")   - foriegn field
 startDate: Date
 endDate: Date
]

So Customer has userLinks and contains multiple users and endDate in user Link is expiration of user with that Customer.
I want to aggregate on user collection as I have to search on description field and I want to look up with customer and to get all customers connected with that user and one more condition we have to consider is that endDate should not be there for that user.
I am able to join but not able incorporate the second condition i.e. endDate should not exist for that user.
Solution I tried:-

[
  {
    $match:
      /**
       * query: The query in MQL.
       */
      {
        "user.description": "XYZ",
      },
  },
  {
    $lookup:
      /**
       * from: The target collection.
       * localField: The local join field.
       * foreignField: The target join field.
       * as: The name for the results.
       * pipeline: Optional pipeline to run on the foreign collection.
       * let: Optional variables to use in the pipeline field stages.
       */
      {
        from: "customer",
        let: {
          user_id: "$_id",
        },
        pipeline: [
          {
            $match: {
              $expr: {
                $eq: [
                  "$$user_id",
                  "$userLink.userId",
                ],
              },
            },
          },
        ],
        as: "customers",
      },
  },
2

Please provide real sample documents that we can cut-n-paste to experiment with your issue.

3

Hi Steeve,
I am attaching the sample collection with few reords.
User collection contains 3 records and customer contains 2 records.
I am applying aggregation on user collection and try to fetch user and its linked customers. Here “Rahul” user is associated to 2 customers but in one customer it got expired as it had end date. According to below query it fetches both customers with “Rahul” user but I want one customer should be returned in which endDate is not present.

user Collection

[{
  "_id": {
    "$oid": "655b70154dcdd7ef42b19958"
  },
  "name": "Rahul",
  "description": "user 1",
  "address": "noida"
},
{
  "_id": {
    "$oid": "655b70a14dcdd7ef42b19959"
  },
  "name": "Rohit",
  "description": "user 2",
  "address": "delhi"
},
{
  "_id": {
    "$oid": "655b70bc4dcdd7ef42b1995a"
  },
  "name": "Mohit",
  "description": "user 3",
  "address": "noida"
}]

customer collection:-

[{
  "_id": {
    "$oid": "655b70c54dcdd7ef42b1995c"
  },
  "type": "professional",
  "description": "associates",
  "userLinks": [
    {
      "_id": {
        "$oid": "655b70c54dcdd7ef42b1995a"
      },
      "userId": {
        "$oid": "655b70154dcdd7ef42b19958"
      },
      "startDate": {
        "$date": "2023-11-20T00:00:00.000Z"
      }
    },
    {
      "_id": {
        "$oid": "655b70c54dcdd7ef42b1995b"
      },
      "userId": {
        "$oid": "655b70a14dcdd7ef42b19959"
      },
      "startDate": {
        "$date": "2023-11-20T00:00:00.000Z"
      }
    }
  ]
},
{
  "_id": {
    "$oid": "655b743c4dcdd7ef42b19962"
  },
  "type": "stakeholder",
  "description": "associates comp",
  "userLinks": [
    {
      "_id": {
        "$oid": "655b70c54dcdd7ef42b1995a"
      },
      "userId": {
        "$oid": "655b70154dcdd7ef42b19958"
      },
      "startDate": {
        "$date": "2023-11-20T00:00:00.000Z"
      },
      "endDate": {
        "$date": "2023-11-20T00:00:00.000Z"
      }
    },
    {
      "_id": {
        "$oid": "655b70c54dcdd7ef42b1995b"
      },
      "userId": {
        "$oid": "655b70bc4dcdd7ef42b1995a"
      },
      "startDate": {
        "$date": "2023-11-20T00:00:00.000Z"
      }
    }
  ]
}]

Aggregation query:-

[
  {
    $match:
      /**
       * query: The query in MQL.
       */
      {
        name: "Rahul",
      },
  },
  {
    $lookup:
      /**
       * from: The target collection.
       * localField: The local join field.
       * foreignField: The target join field.
       * as: The name for the results.
       * pipeline: Optional pipeline to run on the foreign collection.
       * let: Optional variables to use in the pipeline field stages.
       */
      {
        from: "customer",
        localField: "_id",
        foreignField: "userLinks.userId",
        as: "customers",
      },
  },
]

Expected result:-

So with user Rahul 
only  655b70c54dcdd7ef42b1995c customer should return.

Please let me know if any other details required.

4

Here is something that seems to work. Use at your own risk.

function pipeline()
{
   return [ match_user() , lookup_customers() ] ;
}

function match_user()
{
   const user = { "name" : 'Rahul' } ;

   return { "$match" : user } ;
}

function lookup_customers()
{
   return { "$lookup" : {
      "from" : "customer",
      "localField" : "_id" ,
      "foreignField" : "userLinks.userId" ,
      "let" : { "user" : "$_id" } ,
      "pipeline" : lookup_pipeline() ,
      "as" : "customers",
   } } ;
}

function lookup_pipeline()
{
   return [
      filter_links() ,
      { "$match" : { "_tmp.links" : { "$size" : 1 } } } ,
      { "$unset" : [ "_tmp" ] }
   ] ;
}

function filter_links()
{
   return { "$set" : { "_tmp.links" : { "$filter" : {
      "input" : "$userLinks" ,
      "as" : "link" ,
      "cond" : { "$and" : [
         { "$eq" : [ "$$link.userId" , "$$user" ] } ,
         { "$eq" : [ { "$type" : "$$link.endDate" } , "missing" ] }
      ] } ,
      "limit" : 1
   } } } } ;
}

db.user.aggregate( pipeline() )