Help with lookup and pipeline

Working with Data
#1

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?

#2

Could be nice to include the expected output.

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

#3

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"
	}
]
#4

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
#5

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

#6

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.

#7

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