MongoDB: Filter an array by _id using aggregate

Working with Data
1

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": "mister.paper@papers.org",
    "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": "mister.paper@papers.org"
  }
]

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,
	}
}
], {});
1 Like
2

Hi @duard_N_A

That’s a weird behavior to me (not an expert at all). But you can workaround using $addFields. Because the example is so messy I wrote a simple one:

[
  {
    "_id": {
      "$oid": "603d4c44505a51001c9b936f"
    },
    "internals": {
      "_id": [
        {
          "$oid": "5e94da0f4d499253049ec532"
        },
        {
          "$oid": "5e94da0f4d499253049ec534"
        },
        {
          "$oid": "5f05d9a2bfc8fa1361d8beec"
        },
        {
          "$oid": "603d4c44505a51001c9b936f"
        }
      ]
    }
  }

And a possible code to get what you need is:

db.collection.aggregate({
  "$addFields": {
    "res": {
      $filter: {
        input: "$internals._id",
        as: "i",
        cond: {
          "$eq": [
            "$$i",
            "$_id"
          ]
        }
      }
    }
  }
})

Hopefully, you’ll get an answer from more knowledgeable people here.

1 Like
3

Thanks Santiago, but it not works, what I need is get only one internal that match with profile id

4

I see, i think now you edited and it looks quite different. Good luck anyways.

5

I put the link to mongo playground

6

I think it is almost there, but nnow I need the name of the internal property

db.users.aggregate([
  {
    $unwind: "$profile"
  },
  {
    $unwind: "$profile.products"
  },
  {
    $unwind: "$profile.products.profile"
  },
  {
    "$lookup": {
      from: "products",
      localField: "profile.products.product",
      foreignField: "_id",
      as: "products"
    }
  },
  {
    $unwind: "$products"
  },


	{"$addFields": {
	    "res": {
	      $filter: {
	        input: "$products.internals._id",
	        as: "i",
	        cond: {
	          "$eq": [
	            "$$i",
	            "$profile.products.profile"
	          ]
	        }
	      }
	    }
	  }
	},
{
	'$project': {
		_id: 1, name: 1,
		'profile.products': 1,
		res: 1
	}
},

])
7

Now I only need to get ther name of the INTERNAL

8

Sorry, I’m confused, what is the name of the internal? rename a field?

9

please, look the query on mongoplayground Mongo playground https://mongoplayground.net/p/7r0E6C5X0Xy

10

Thak you Santiago, it works !!!
Mongo Playground

db.users.aggregate([
  { $match: {	email: { $in: [ /carlos.aquino/i ] },	active: { $in: [ true, null ] },	deleted: { $in: [ false, null ] }	} }, { $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"
  },
  {
    "$addFields": {

      "internal": {
        $filter: {
          input: "$products.internals",
          as: "i",
          cond: {
            "$eq": [
              "$$i._id",
              "$profile.products.profile"
            ]
          }
        }
      },
    }
  },
  {
    "$project": {
      _id: 1,
      name: 1,
      "profile.products": 1,
      products: {
        name: 1,
        _id: 1
      },
      internal: { _id : 1, name : 1 },
    }
  }
])
1 Like
11

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.