MongoDB: Project only the items that was queried for in the array?

Question

I have a user document, each user has an array of objects Given an array of item tags, I need to find the user whose item array has the item-tag, and return the entire user object except the items array, in which I only want to return the first item tags that existed in the tagArray that was used for the intial query.

//user document
 {
    user: 'John',
    items: [ObjectId('ABC'), ObjectId('123') ...]
 }

//item document
{
  _id: ObjectId('ABC'),
  tag: 'some-unique-id'
}, 
{
  _id: ObjectId('DEF'),
  tag: 'some-unique-tag'
}

Users have a 1-to-N relationship with items, the items may repeat within the User's items array.

This is what I current have, which returns the entire user object, but also all the items within the array.

const tagArray = [ 'some-unique-id', 'some-unique-tag']
items.aggregate([
  { $match: { 'tag': { $in: tagArray } }},
  { $lookup: {
        from: "users",
        localField: "tag",
        foreignField: '_id',
        as: 'userInfo'
       }
  },
  {
     $project: {??}  //<--- I'm pretty sure I'm missing something in the project
])

Outcome that I have now:

{
 _id: ObjectId('ABC'),
  tag: 'some-unique-id'
  userInfo : [ {user: 'John', items: [ObjectId('ABC'), ObjectId('123') ...] }]
}

What I want to achieve:

{
 _id: ObjectId('ABC'),
  tag: 'some-unique-id'
  userInfo : [ {user: 'John', items: [ObjectId('ABC')]} ]
}

Edit: There is a similar question here : Retrieve only the queried element in an object array in MongoDB collection

However in my case, I need the filter condition to be "one of the the tags that is in the tagArray.

Any suggestion or pointers would be appreciated, thank you!

Does this answer your question? [Retrieve only the queried element in an object array in MongoDB collection](https://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) As stated there you need to use `$filter` to filter the array to get required object out ! — whoami - fakeFaceTrueSoul, May 14 '20 at 22:34
It was close but not exactly. I have a tagArray that contains multiple tags, I'd need the condition to equal "one of the tags within the tagArray" , which is dynamic and I wouldn't be able to hard code it in — fishcakesAndCoffee, May 14 '20 at 22:50
Please edit with all the needed information : Sample docs, inputs & required o/p.. — whoami - fakeFaceTrueSoul, May 14 '20 at 22:51
Adjusted, hope this does a better job in conveying the question — fishcakesAndCoffee, May 14 '20 at 22:57
@fishcakesAndCoffee lookup result could get you the multiple users as well? or just single user every time? — harshit kohli, May 14 '20 at 23:38
Need some clarification here: for the item with `_id: ObjectId('ABC'), 'some-unique-id'`, you will get users that have one of the items with `ObjectId('ABC')`, if you have included other tag in the query, let's say `'some-other-unique-tag'`. What's the logic to choose which `_id` gets chosen? If you want any one of them why not just choose the current tag? Or do you have to exclude `ObjectId('ABC')` ? — thammada.ts, May 15 '20 at 11:34
@harshitkohli Look up result should get the same number of users as the length of the `tagArray`, unless there was a server error. — fishcakesAndCoffee, May 15 '20 at 14:06
@TheeSritabtim I hope I understood you correctly, but essentially what happens is I have an array of these tags, and some users might have one of them. I would like to return all the users that have any of these tags. In the case that ObjectId('ABC') already exists in user A's items, the chance of it to exist in user B's item is very very low, and thus I am choosing to ignore that edge case for now. — fishcakesAndCoffee, May 15 '20 at 14:10
@fishcakesAndCoffee I thought, for item ObjectId('ABC') you were looking up users that have ObjectId('ABC') in tags. But when I checked again, I saw you are joing `item.tag` with `user._id`. In that case, don't you get just 1 user per item, because user._id is unique? — thammada.ts, May 15 '20 at 15:53
@TheeSritabtim user._id is unique, and yes, with the join I get 1 user per item. The challenge is the user that is queried via the ObjectId('ABC') has an array of items, where Object('ABC') would be one of them. I would like to project / alter the user to instead of returning the entire array of items, only return the array with the item that matches ObjectId('ABC') — fishcakesAndCoffee, May 15 '20 at 16:09
@fishcakesAndCoffee from your latest comment, it seems like user that is inside item `ObjectId('ABC')`, will always has ObjectId('ABC') in the items array, so why not just use `items: [ObjectId('ABC')]` — thammada.ts, May 15 '20 at 16:17
Do you mean to further handle the data after the query returns? That is an option, but I was hoping to complete the data cleanup before letting the server process it. If you mean to do `items: [ObjectId('ABC')] within the `$project`, I'm not sure how to do that, as when the query runs, no one knows whats within the `tagArray` that is used to search for the items — fishcakesAndCoffee, May 15 '20 at 16:26

score 2 · Answer 1 · answered May 15 '20 at 01:24

I don't know if I understood well what you need, but I think this is a good start (maybe you can modify it by yourself):

Test data:

 // users collection
  [
    {
      user: "John",
      items: [
        ObjectId("5a934e000102030405000002"),
        ObjectId("5a934e000102030405000003")
      ]
    }
  ]  

 // items collection
  [
    {
      _id: ObjectId("5a934e000102030405000002"),
      tag: "some-unique-id"
    },
    {
      _id: ObjectId("5a934e000102030405000009"),
      tag: "some-unique-tag"
    }
  ]
}

Query:

db.users.aggregate([
  {
    $lookup: {
      from: "items",
      localField: "items",
      foreignField: "_id",
      as: "userInfo"
    }
  },
  // create new fields inside the userInfo array
  {
    $project: {
      "userInfo.user": "$user",
      "userInfo.items": "$items",
      "tag": {
        $arrayElemAt: ["$userInfo.tag", 0]
      }
    }
  },
  // filter the userInfo.items field, based on _id field
  // it's important to use $arrayElemAt here 
  {
    $addFields: {
      "userInfo.items": {
        $filter: {
          input: {
            $arrayElemAt: [
              "$userInfo.items",
              0
            ]
          },
          as: "i",
          cond: {   
            $in: [
              "$$i",
              [
                "$_id" 
              ]
            ]
          }
        }
      }
    }
  }
])

Result:

[
  {
    "_id": ObjectId("5a934e000102030405000002"),
    "tag": "some-unique-id",
    "userInfo": [
      {
        "items": [
          ObjectId("5a934e000102030405000002")
        ],
        "user": "John"
      }
    ]
  }
]

MongoDB: Project only the items that was queried for in the array?

1 Answers1