Is there any way to $lookup with embed array of document?

Working with Data
,
1

This is my collection:

code
code948×2980 352 KB

I have collection color, so i want to join product with color and don’t know how. Is there anyone can help me !

2

Hi @Tri_Vo_Van ,

Do you mean just based on the “inventory.color” field of each embedded array obj?

The following aggregation should work for each document:

[{$lookup: {
 from: 'color',
 localField: 'inventory.color',
 foreignField: 'name',
 as: 'color'
}}]

For example with the following data:

db.product.find();
{ _id: ObjectId("621b6a9d63c5b12f4cda3db0"),
  name: 'bike',
  inventory: 
   [ { size: 'XL', color: 'black' },
     { size: 'XL', color: 'blue' } ] }
{ _id: ObjectId("621b6a9d63c5b12f4cda3db1"),
  name: 'car',
  inventory: 
   [ { size: 'SUV', color: 'black' },
     { size: 'Private', color: 'white' } ] }

db.color.find()
{ _id: ObjectId("621b6aff63c5b12f4cda3db2"),
  name: 'white',
  added: 2022-02-27T12:13:51.891Z }
{ _id: ObjectId("621b6aff63c5b12f4cda3db3"),
  name: 'black',
  added: 2022-02-27T12:13:51.891Z }
{ _id: ObjectId("621b6aff63c5b12f4cda3db4"),
  name: 'blue',
  added: 2022-02-27T12:13:51.891Z }

db.product.aggregate([{$lookup: {
 from: 'color',
 localField: 'inventory.color',
 foreignField: 'name',
 as: 'color'
}}])
{ _id: ObjectId("621b6a9d63c5b12f4cda3db0"),
  name: 'bike',
  inventory: 
   [ { size: 'XL', color: 'black' },
     { size: 'XL', color: 'blue' } ],
  color: 
   [ { _id: ObjectId("621b6aff63c5b12f4cda3db3"),
       name: 'black',
       added: 2022-02-27T12:13:51.891Z },
     { _id: ObjectId("621b6aff63c5b12f4cda3db4"),
       name: 'blue',
       added: 2022-02-27T12:13:51.891Z } ] }
{ _id: ObjectId("621b6a9d63c5b12f4cda3db1"),
  name: 'car',
  inventory: 
   [ { size: 'SUV', color: 'black' },
     { size: 'Private', color: 'white' } ],
  color: 
   [ { _id: ObjectId("621b6aff63c5b12f4cda3db2"),
       name: 'white',
       added: 2022-02-27T12:13:51.891Z },
     { _id: ObjectId("621b6aff63c5b12f4cda3db3"),
       name: 'black',
       added: 2022-02-27T12:13:51.891Z } ] }

Is that it?

Ty
Pavel

3

I want instead of inventory.color is a Object Id, it need it to be a document that is looked up from color collection like this

image
image1920×1080 208 KB

I currently use unwind and group to archieve the result in first image, but I don’t think it is the best way to archieve that

4 
const product = await Product.aggregate([
			{
				$match: {
					_id: Types.ObjectId(req.params.id),
				},
			},
			{
				$unwind: '$inventory',
			},
			{
				$lookup: {
					from: 'colors',
					localField: 'inventory.color',
					foreignField: '_id',
					as: 'inventory.color',
				},
			},
			{
				$unwind: '$inventory.color',
			},
			{
				$unwind: '$inventory.size',
			},
			{
				$lookup: {
					from: 'sizes',
					localField: 'inventory.size',
					foreignField: '_id',
					as: 'inventory.size',
				},
			},
			{
				$unwind: '$inventory.size',
			},
			{
				$group: {
					_id: '$_id',
					name: {
						$first: '$name',
					},
					images: {
						$first: '$images',
					},
					category: {
						$first: '$category',
					},
					brand: {
						$first: '$brand',
					},
					description: {
						$first: '$description',
					},
					available: {
						$first: '$available',
					},
					unit: {
						$first: '$unit',
					},
					price: {
						$first: '$price',
					},
					inventory: {
						$push: '$$ROOT.inventory',
					},
				},
			},
		])
This is my code to archieve result now
5

I am trying to work something out with $filter and $map but I cannot ignore the fact that this schema looks very relational, and require you to join multiple collections to get one set of results.

Why don’t you store the size, color objects with the needed information embeded in the product ? This will make you life much easier and will give you the benifits of using MongoDB. (I hope its not due to restrications you caused yourself using Mongoose…)

6

I just want to make color have a custom name, and that name must be unique for every color code, so the user just need create a color, and reuse it in the next time without mistake. Seem like I have to abandon that idea, thanks you!

7

So let me get that, you store a color id in the main collection and this id is mapped to a custom color name?

Why can’t it be saved in a single place with a new field called “custom_name”?

8

Because I want to make dropdown list, that dropdown list contains color which user created, and user just choose whatever user create.

This is for creating a new product, you have to choose color and size from a dropdown list, it is not good to hard code the dropdown list, there must be place for user to adjust that dropdown list.

9

So why don’t you query the drop down from another collection once user presses the drop down, while the choosen one is embedded in the product document?

User get the product with no custom data > query product > user click dropdown data is fetched from another colour collection based on the options > user create a custom one this data is now stored in product (per user)…

1 Like
10

Thank god, you are my savior, thank you so much, that definitely is what I need !!!

1 Like
11

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