I have two collections, one called experiences and trips.
i am trying to get all of the experience data to trip via object id
i was able to aggregate using $unwind, $lookup pipeline and $groupBy.
Unfortunately, some data is missing due $lookup because lookup data is replaced.
i want that data to be kept, with lookup data
experience:ObjectId(‘….’), —> lookup
featuredExperience{…} —> missing
This is trip data :
{
"experience": {
"$oid": "66896213e471a970deb75f4e"
},
"featuredExperience": {
"body": "Make sure to edit the title and body in experience page",
"keyword": "Default",
"keywordIndex": "0",
"image": "https://purpledev.s3.us-east-2.amazonaws.com/itinerary/3717bd11-0f64-44d5-9122-0be172183d34.jpeg"
},
"_id": {
"$oid": "6689845e1d5ea90fe43fae11"
}
}
],
"_id": {
"$oid": "66892a6fe471a970deb73229"
}
},
{
"date": "2024-07-07T11:28:39.406Z",
"experiencesPerDay": [
{
"experience": {
"$oid": "66896213e471a970deb75f4e"
},
"featuredExperience": {
"body": "",
"keyword": "",
"keywordIndex": "0",
"image": ""
},
"_id": {
"$oid": "6689845e1d5ea90fe43fae13"
}
}
],
"_id": {
"$oid": "66892a6fe471a970deb7322a"
}
}
],
}
This is aggregate:
{
$unwind:
/**
* path: Path to the array field.
* includeArrayIndex: Optional name for index.
* preserveNullAndEmptyArrays: Optional
* toggle to unwind null and empty values.
*/
{
path: "$experiences",
preserveNullAndEmptyArrays: true
}
},
{
$lookup:
/**
* from: The target collection.
* localField: The local join field.
* foreignField: The target join field.
* as: The name for the results.
* pipeline: Optional pipeline to run on the foreign collection.
* let: Optional variables to use in the pipeline field stages.
*/
{
from: "experiences",
localField:
"experiences.experiencesPerDay.experience",
foreignField: "_id",
as: "experiences.experiencesPerDay"
}
},
{
$group:
/**
* _id: The id of the group.
* fieldN: The first field name.
*/
{
_id: "$_id",
tour: {
$first: "$tour"
},
itinerary: {
$first: "$itinerary"
},
experiences: {
$push: "$experiences"
},
costing: {
$first: "$costing"
}
}
Result:
{
"_id": {
"$oid": "66892a6fe471a970deb73228"
"experiences": [
{
"date": "2024-07-06T11:28:39.406Z",
"experiencesPerDay": [
{
"_id": {
"$oid": "6686939dff9fd19d177bbf09"
},
"organization": {
"$oid": "668246c4a731ece7810d291c"
},
"titles": [
{
"name": "Street Burger - Bambalapitiya, Ramya Road, Colombo, Sri Lanka",
"language": "en",
"_id": {
"$oid": "6686939dff9fd19d177bbf0a"
}
},
{
"name": "hamburger de rue - Bambalapitiya, Ramya Road, Colombo, Sri Lanka",
"language": "fr",
"_id": {
"$oid": "6686939dff9fd19d177bbf0b"
}
},
{
"name": "Street Burger - Bambalapitiya, Ramya Road, Colombo, Sri Lanka",
"language": "es",
"_id": {
"$oid": "6686939dff9fd19d177bbf0c"
}
}
],
"area": "Ramya Road, Colombo, Sri Lanka",
"name": "Street Burger - Bambalapitiya",
"location": {
"latitude": "6.8892294",
"longitude": "79.8543416"
},
"apiId": "ChIJD7tuZMFb4joRJoFHG8FGMcc",
"featured": {
"body": "",
"keyword": "",
"keywordIndex": "",
"image": ""
},
"entryType": "google",
"images": [
"https://purpledev.s3.us-east-2.amazonaws.com/itinerary/b51189d6-d7a4-40a9-a64c-aed743c88597.jpeg"
],
"active": true,
"contexts": [
{
"body": "test 2",
"keyword": "Nature ",
"language": "fr",
"_id": {
"$oid": "6686953dff9fd19d177bbf73"
}
},
{
"body": "test 1",
"keyword": "Nature ",
"language": "fr",
"_id": {
"$oid": "66869447ff9fd19d177bbf61"
}
},
{
"body": "Come fun topday",
"keyword": "test 1",
"language": "en",
"_id": {
"$oid": "6686955eff9fd19d177bbf86"
}
}
],
"createdAt": {
"$date": "2024-07-04T12:20:45.933Z"
},
"updatedAt": {
"$date": "2024-07-06T16:58:03.007Z"
},
"__v": 0,
"cost": 100,
"notes": "Come Vsite"
},
{
"_id": {
"$oid": "66896213e471a970deb75f4e"
},
"organization": {
"$oid": "668246c4a731ece7810d291c"
},
"titles": [
{
"name": "Colombo City Centre Mall and Residences, Sir James Pieris Mawatha, Colombo, Sri Lanka",
"language": "en",
"_id": {
"$oid": "66896213e471a970deb75f4f"
}
},
{
"name": "Colombo City Centre Mall and Residences, Sir James Pieris Mawatha, Colombo, Sri Lanka",
"language": "fr",
"_id": {
"$oid": "66896213e471a970deb75f50"
}
},
{
"name": "Colombo City Centre Mall and Residences, Sir James Pieris Mawatha, Colombo, Sri Lanka",
"language": "es",
"_id": {
"$oid": "66896213e471a970deb75f51"
}
}
],
"area": "Sir James Pieris Mawatha, Colombo, Sri Lanka",
"name": "Colombo City Centre Mall and Residences",
"location": {
"latitude": "6.9173554",
"longitude": "79.8550325"
},
"apiId": "ChIJseUqHGtZ4joRgteF9GKSLoc",
"contexts": [
{
"body": "Make sure to edit the title and body in experience page",
"keyword": "Default",
"language": "en",
"_id": {
"$oid": "66896213e471a970deb75f52"
}
}
],
"images": [
"https://purpledev.s3.us-east-2.amazonaws.com/itinerary/3717bd11-0f64-44d5-9122-0be172183d34.jpeg"
],
"cost": 100,
"notes": "",
"active": true,
"createdAt": {
"$date": "2024-07-06T15:26:11.149Z"
},
"updatedAt": {
"$date": "2024-07-06T17:52:30.461Z"
},
"__v": 0
}
],
"_id": {
"$oid": "66892a6fe471a970deb73229"
}
},
{
"date": "2024-07-07T11:28:39.406Z",
"experiencesPerDay": [
{
"_id": {
"$oid": "66896213e471a970deb75f4e"
},
"organization": {
"$oid": "668246c4a731ece7810d291c"
},
"titles": [
{
"name": "Colombo City Centre Mall and Residences, Sir James Pieris Mawatha, Colombo, Sri Lanka",
"language": "en",
"_id": {
"$oid": "66896213e471a970deb75f4f"
}
},
{
"name": "Colombo City Centre Mall and Residences, Sir James Pieris Mawatha, Colombo, Sri Lanka",
"language": "fr",
"_id": {
"$oid": "66896213e471a970deb75f50"
}
},
{
"name": "Colombo City Centre Mall and Residences, Sir James Pieris Mawatha, Colombo, Sri Lanka",
"language": "es",
"_id": {
"$oid": "66896213e471a970deb75f51"
}
}
],
"area": "Sir James Pieris Mawatha, Colombo, Sri Lanka",
"name": "Colombo City Centre Mall and Residences",
"location": {
"latitude": "6.9173554",
"longitude": "79.8550325"
},
"apiId": "ChIJseUqHGtZ4joRgteF9GKSLoc",
"contexts": [
{
"body": "Make sure to edit the title and body in experience page",
"keyword": "Default",
"language": "en",
"_id": {
"$oid": "66896213e471a970deb75f52"
}
}
],
"images": [
"https://purpledev.s3.us-east-2.amazonaws.com/itinerary/3717bd11-0f64-44d5-9122-0be172183d34.jpeg"
],
"cost": 100,
"notes": "",
"active": true,
"createdAt": {
"$date": "2024-07-06T15:26:11.149Z"
},
"updatedAt": {
"$date": "2024-07-06T17:52:30.461Z"
},
"__v": 0
}
],
"_id": {
"$oid": "66892a6fe471a970deb7322a"
}
}
],
"costing": {
"totalCost": 200
},
"createdAt": {
"$date": "2024-07-06T11:28:47.909Z"
},
"featured": {
"description": "",
"image": ""
}
}
========================================================
Intended Result.
{
"_id": {
"$oid": "66892a6fe471a970deb73228"
},
"experiences": [
{
"date": "2024-07-06T11:28:39.406Z",
"experiencesPerDay": [
{
"featuredExperience": {
"body": "Make sure to edit the title and body in experience page",
"keyword": "Default",
"keywordIndex": "0",
"image": "https://purpledev.s3.us-east-2.amazonaws.com/itinerary/3717bd11-0f64-44d5-9122-0be172183d34.jpeg"
},
"_id": {
"$oid": "6686939dff9fd19d177bbf09"
},
"organization": {
"$oid": "668246c4a731ece7810d291c"
},
"titles": [
{
"name": "Street Burger",
"language": "en",
"_id": {
"$oid": "6686939dff9fd19d177bbf0a"
}
},
{
"name": "hamburger de rue",
"language": "fr",
"_id": {
"$oid": "6686939dff9fd19d177bbf0b"
}
},
{
"name": "Street Burger",
"language": "es",
"_id": {
"$oid": "6686939dff9fd19d177bbf0c"
}
}
],
"location": {
"latitude": "6.8892294",
"longitude": "79.8543416"
},
"apiId": "ChIJD7tuZMFb4joRJoFHG8FGMcc",
"entryType": "google",
"images": [
"https://purpledev.s3.us-east-2.amazonaws.com/itinerary/b51189d6-d7a4-40a9-a64c-aed743c88597.jpeg"
],
"active": true,
"contexts": [
{
"body": "test 2",
"keyword": "Nature ",
"language": "fr",
"_id": {
"$oid": "6686953dff9fd19d177bbf73"
}
},
{
"body": "test 1",
"keyword": "Nature ",
"language": "fr",
"_id": {
"$oid": "66869447ff9fd19d177bbf61"
}
},
{
"body": "Come fun topday",
"keyword": "test 1",
"language": "en",
"_id": {
"$oid": "6686955eff9fd19d177bbf86"
}
}
],
"createdAt": {
"$date": "2024-07-04T12:20:45.933Z"
},
"updatedAt": {
"$date": "2024-07-06T16:58:03.007Z"
},
"__v": 0,
"cost": 100,
"notes": "Come Vsite"
},
],
"_id": {
"$oid": "66892a6fe471a970deb7322a"
}
}
],
}
here is a image to understand
Intended Result here