MongoDb aggregation lookup finding document with properties value included in array of another colle-CodePudding

I'm new to mongodb aggregation and I'm not able to extract all docuemnts from a collection that as a field value that is included in an array of another collection.

let's say I have a collection 'users' with documets like:

{
user: 'foo',
urls: ['/url1', '/url2', '/url3']
}

and another collection 'menu' with docuemnts like:

{
name: 'bar',
link: '/url1234',
component: 'layout'
}
{
name: 'baz',
link: '/url454',
component: 'layout'
}

The desired result from the above scenario is

{
name: 'bar',
link: '/url1234'
}

I'm using a pipeline like this but I'm stacked in getting back only the documetns where the url from users collection is included in link field from the menu collection

     '$match': {
        'user': 'foo'
      }
    }, {
      '$project': {
        'urls': 1, 
        '_id': 0
      }
    }, {
      '$lookup': {
        'from': 'menu', 
        'pipeline': [
          {
            '$match': {
              'component': 'layout'
            }
          }
        ], 
        'as': 'results'
      }
    }
  ]

CodePudding user response：

Use $indexOfCP to perform substring search in your sub-pipeline. The $indexOfCP functions will return -1 if the substring is not found. Use $ne to reject such case to obtain substring matched records(i.e. those you desired, as /url1 is a substring of /url1234)

db.users.aggregate([
  {
    $match: {
      user: "foo"
    }
  },
  {
    $unwind: "$urls"
  },
  {
    "$lookup": {
      "from": "menu",
      "let": {
        url: "$urls"
      },
      "pipeline": [
        {
          "$match": {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$component",
                    "layout"
                  ]
                },
                {
                  $ne: [
                    -1,
                    {
                      "$indexOfCP": [
                        "$link",
                        "$$url"
                      ]
                    }
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "menuLookup"
    }
  },
  {
    "$unwind": "$menuLookup"
  },
  {
    "$replaceRoot": {
      "newRoot": "$menuLookup"
    }
  },
  {
    "$unset": "component"
  }
])

Mongo Playground