MongoDB: Filter an array by _id using aggregate

Question

I need some help to filter the field Internals array to only show the item with the same _id of this field profile_id using aggregate, but I realy cant get this.

I try use filter, lookup and search in many posts.

I want using aggregate, to get the documents with filtered array of users based on the objectID of the profile.

Every profile id has only one internal id

Each user has only one INTERNAL.

Mongo PLayGround Query

PRODUCTS TABLE

 [
      {
        "_id": {"$oid": "5dd5a66e8ddbd07fa251b88e"},
        "name": "Product One",
        "internals": [
          {
            "default": false,
            "_id": {"$oid": "5e94da0f4d499253049ec532"},
            "name": "Type A"
          },
          {
            "_id": {"$oid": "5e94da0f4d499253049ec534"},
            "name": "Type B"
          },
          {
            "_id": {"$oid": "5e94da0f4d499253049ec536"},
            "name": "Type C"
          },
          {
            "_id": {"$oid": "5e94da0f4d499253049ec538"},
            "name": "Type D"
          },
          {
            "_id": {"$oid": "5f05d9a2bfc8fa1361d8beea"},
            "name": "Type E"
          },
          {
            "_id": {"$oid": "5f05d9a2bfc8fa1361d8beec"},
            "name": "Type F"
          }
        ]
      },
      {
        "_id": {"$oid": "5e8b40bc91dd0e12e0219578"},
        "name": "Product Two",
        "internals": [
          {
            "_id": {"$oid": "5e8b40bc91dd0e12e0219579"},
            "name": "Default Type"
          }
        ],
      }
    ]

USER TABLE

[
  {
    "_id": {"$oid": "603d4c44505a51001c9b936f"},
    "email": "[email protected]",
    "name": "Mister Paper",
    "profile": {
      "products": [
        {
          "_id": {"$oid": "603d4d101c24a9001cf79db1"},
          "product": {"$oid": "5dd5a66e8ddbd07fa251b88e"},
          "profile": {"$oid": "5e94da0f4d499253049ec534"}
        },
        {
          "_id": {"$oid": "603fe3ebca063ea357ca0666"},
          "product": {"$oid": "5e8b40bc91dd0e12e0219578"},
          "profile": {"$oid": "5e8b40bc91dd0e12e0219579"}
        },
        {
          "_id": {"$oid": "603fe3ebca063ea357ca0667"},
          "product": {"$oid": "5dd5a66e8ddbd07fa251b88e"},
          "profile": {"$oid": "5e94da0f4d499253049ec538"}
        }
      ],
    },
    "username": "[email protected]"
  }
]

That is my query at this moment after try many others :

QUERY

db.getCollection('users').aggregate([
{ $match: { email: { $in: [ /mister.paper/i ] }} }, { $sort: { created_at: -1 } },

{$unwind:"$profile"},
{$unwind:"$profile.products"},
{$unwind:"$profile.products.profile"},

{'$lookup': { from: 'products', localField: 'profile.products.product', foreignField: '_id', as: 'products' } },
{$unwind:"$products"},
//{$unwind:"$products"},
//{$unwind:"$products.internals"},
{$addFields: { "products.profile_id": '$profile.products.profile'}},

{'$lookup': { from: 'products.internals', localField: 'products.profile_id', foreignField: '_id', as: 'internalsArray' } },

{
    '$project': {
    name: 1,
    products: {
      _id: "$products._id",
      name: "$products.name",
      profile_id : 1,
    },
    productsId: 1,
    "internals": { _id: "$products.internals._id" , name: "$products.internals.name" },
        internalsArray : 1,
    }
}
], {});

What I realy need is to get only the INTERNALS._ID that match with profile.products.profile (ObjectId) or profile_id ... this is very hard to do ... yes, I try $filter and $unwind — duardbr
– duardbr, Commented Jul 6, 2021 at 19:49
My user product has a profile id 5e94da0f4d499253049ec534 I want to get only the internal with the id 5e94da0f4d499253049ec534 for that user on that product — duardbr
– duardbr, Commented Jul 6, 2021 at 19:55
Can you provide the true shape of your documents. What you provided is the result of your query. For all the collections that are being used. — Hussam
– Hussam, Commented Jul 6, 2021 at 19:56

Hussam · Accepted Answer · 2021-07-07 13:30:10Z

I used $filter to filter out products internal array by matching internal._id and profile.products.profile. You can extend this query to further shape your data in desired format.

Here's the core pipeline that performs filtration. I used it after your {$unwind: "$products"} stage.

{
    "$addFields": {
      "products.internals": {
        $filter: {
          input: "$products.internals",
          as: "internal",
          cond: {
            $eq: [
              "$$internal._id",
              "$profile.products.profile"
            ]
          },
          
        }
      }
    }
  }

Mongo Playground: https://mongoplayground.net/p/zPxE-H2mYc7

A suggestion after a brief look on your sample data is your first unwind {$unwind:"$profile"} and {$unwind:"$profile.products.profile"} doesn't make sense since profile is an object in first unwind and ObjectId in second unwind so you can remove them.

Collectives™ on Stack Overflow

MongoDB: Filter an array by _id using aggregate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related