Help with lookup and pipeline

I have this two example of collection:
Collection 1

_id : 5fa2bf16987ad021094dbf6f,
date : 2020-11-05T08:18:00.000+00:00,
idTwo : 5f9925c095b8a429083fb45d,
prest : 2

Collection 2

_id : 5f9925c095b8a429083fb45d,
idAgenda : [
	{	
		id : 1,
		price: 122,
		enable : true,
		label : "label 1"
	},{
		id : 2,
		price: 25,
		enable : true,
		label : "label 2"
	},{
		id : 3,
		price: 149,
		enable : true,
		label : "label 3"
	}],
Surname: "Surname",
Name: "Name

from _id of collection 1, I would like to merge collection 1 with collection 2
where idTwo of collection1 is = _id of collection2
and idAgenda of collection 2 is = prest of collection 1

I suppose that I need pipeline (for $unwind for example idAgenda), but I don’ t understand how use it.
I have try with this $lookup:

{
  from: 'Collection2',
  localField: 'idTwo',
  foreignField: '_id',
  as: 'string'
}

But in **string ** I have an array with all collection2…I need only the Collection2.idAgenda can match with Collection1.prest.
Do you have suggestion for me?

Could be nice to include the expected output.

Btw, collection2 is missing the _id i guess that’s just a typo.

Think now I got what you mean,

This pipeline

[
	{
		"$lookup" : {
			"from" : "Collection2",
			"localField" : "idTwo",
			"foreignField" : "_id",
			"as" : "lookupRes"
		}
	},
	{
		"$addFields" : {
			"prest" : "$lookupRes.idAgenda",
			"idTwo" : "$lookupRes._id"
		}
	},
	{
		"$unwind" : "$prest"
	},
	{
		"$unwind" : "$idTwo"
	},
	{
		"$unset" : "lookupRes"
	}
]

Sorry, id in Collection2 is _id. I modify it.
My excepted output is:

_id : 5fa2bf16987ad021094dbf6f,
date : 2020-11-05T08:18:00.000+00:00,
idTwo : 5f9925c095b8a429083fb45d,
prest : 2,
idAgenda: {
		id : 2,
		price: 25,
		enable : true,
		label : "label 2"
	},
Surname: "Surname",
Name: "Name

Hi @Santiago_Miranda,
no in result I need only one “json”: Collection2.idAgenda.id = Collection1.prest

Maybe this one?

@Francesco_Di_Battist

Otherwise I hope someone else help :slight_smile:

[
	{
		"$lookup" : {
			"from" : "Collection2",
			"localField" : "idTwo",
			"foreignField" : "_id",
			"as" : "lookupRes"
		}
	},
	{
		"$unwind" : "$lookupRes"
	},
	{
		"$addFields" : {
			"Surname" : "$lookupRes.Surname",
			"Name" : "$lookupRes.Name",
			"idAgenda" : {
				"$filter" : {
					"input" : "$lookupRes.idAgenda",
					"as" : "obj",
					"cond" : {
						"$eq" : [
							"$$obj.id",
							"$prest"
						]
					}
				}
			}
		}
	},
	{
		"$unwind" : "$idAgenda"
	},
	{
		"$unset" : "lookupRes"
	}
]

Output

{
	"_id" : "5fa2bf16987ad021094dbf6f",
	"date" : "2020-11-05T08:18:00.000+00:00",
	"idTwo" : "5f9925c095b8a429083fb45d",
	"prest" : 2,
	"Surname" : "Surname",
	"Name" : "Name",
	"idAgenda" : 
		{
			"id" : 2,
			"price" : 25,
			"enable" : true,
			"label" : "label 2"
		}
	
}

Another option may be to use $project, I didn’t try that.

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