Mongodb aggregate two collection

Working with Data
1

Hi Everyone,

I’m new in mongodb and i want to create the collection from the pipeline aggregate.
I have 2 collections with this schema

1 - customer collection :

{ 
   customerId
   audienceId
  integrationDate
  processingDate
}

2 - audience Audience collection

{
	audienceId, 
	name,
	description,
	createdDate,
	updatedDate,
	numberOfBurn,
	offerValidityDays,
	category,
	buCode
}

And my goal is to have the third collection like this:

{
customerId,
buCode,
audiences 
	[
		{
		  audience : {
			audienceId,
			name,
			description,
			createdDate,
			updatedDate,
			numberOfBurn,
			offerValidityDays ,
			category,
			codeBU
		  },
		  processingDate,
		  integrationDate,
		}
]
}

I tried this way but it’s not exactly the same

db.customer.aggregate([
    {
      $lookup:
          {
              from:"audience",
              localField:"audienceId",
              foreignField: "audienceId",
              as: "audiences"
          },
          pipeline:[
              {}
          ]
    }
]).pretty()

Someone can help me please.

2

Please read formatting code and documents and post real sample documents from but input collections. To experiment we have to have real documents to work with. We could manually create some from your schema but it is tedious.

Real sample output documents is also a must.

3

@Mohamed_DIABY, and followup on this? We are still waiting for real sample documents rather than a schema.

7

Hi,

Sorry for the late answer.
here the document

1- the first document audience:

{
		"id":"5234",
		"name":"Simulation SDB",
		"createdDate":"",
		"updatedDate":"",
		"numberOfBurn":"",
		"offerValidityDays":"",
		"category":""
},
{
		"id":"2334",
		"name":"Cusine renovation",
		"createdDate":"",
		"updatedDate":"",
		"numberOfBurn":"",
		"offerValidityDays":"",
		"category":""
}

2- the second document

{
	id:1233,
	audienceId: 5234,
	entryDate:"2022/12/02",
	updateDate": "2022/12/02",
	burnRemaining": "3",
	dateOfLastBurn": "2022/12/02" 
},
{
	id:234,
	audienceId: 2334,
	entryDate:"2022/12/02",
	updateDate": "2022/12/02",
	burnRemaining": "3",
	dateOfLastBurn": "2022/12/02" 
},
{
	id:234,
	audienceId: 5234,
	entryDate:"2022/12/02",
	updateDate": "2022/12/02",
	burnRemaining": "3",
	dateOfLastBurn": "2022/12/02" 
}

3- I would like to get this document by pipeline aggregate

{
  id:"1233",
  buCode:"LMFR",
  audiences[
	{
	  audience:{
		id:"5234",
		name:"Simulation SDB",
		createdDate:"",
		updatedDate:"",
		numberOfBurn:"",
		offerValidityDays:"",
		category:""
	  },
	  createdDate:"",
	  updateDate: "",
	  burnRemaining: "",
	  dateOfLastBurn: "" 
	},
	  audiences[
	{
	  audience:{
		id:"2334",
		name:"Cusine renovation",
		createdDate:"",
		updatedDate:"",
		numberOfBurn:"",
		offerValidityDays:"",
		category:"LOYALTY"
	  },
	  createdDate:"",
	  updateDate: "",
	  burnRemaining: "",
	  dateOfLastBurn: "" 
	}
	]
},
{
  id:"1233",
  buCode:"LMFR",
  audiences[
	{
	  audience:{
		id:"5234",
		name:"Simulation SDB",
		createdDate:"",
		updatedDate:"",
		numberOfBurn:"",
		offerValidityDays:"",
		category:""
	  },
	  createdDate:"",
	  updateDate: "",
	  burnRemaining: "",
	  dateOfLastBurn: "" 
	}
	]
}

Thanks

8

What I have notice so far is that in one collection you have id being a string and in the other it is a number. The string “5234” is not the same as the number 5234.

The you want the id 1233, the number as in

to become id “1233” the string as in

In the result document you show buCode:“LMFR” but I do not see any source field with this field name or value. Please provide real sample documents rather than made-up or redacted document.

You result documents are not valid JSON, so we have a hard time to know what you really want. Make sure the semicolons, the braces and brackets are all correct.