I'm trying to add an item to array in aggregation, but seem to be stuck with it for a couple of days. Here is what it looks like:

db={
  Students: [
    {
      "id": "123",
      "title": "John",
      "marks_in_subjects": [
        {
          "marks": 90,
          "subject_id": "abc"
        },
        {
          "marks": 92,
          "subject_id": "def"
        }
      ]
    }
  ],
  Subjects: [
    {
      "id": "abc",
      "name": "math"
    },
    {
      "id": "def",
      "name": "physics"
    }
  ]
}

And I do the project and concat title to new field like this:

db.Students.aggregate([
  {
    $unwind: "$marks_in_subjects"
  },
  {
    "$lookup": {
      "from": "Subjects",
      "localField": "marks_in_subjects.subject_id",
      "foreignField": "id",
      "as": "subjects"
    }
  },
  {
    $unwind: "$subjects"
  },
  {
    $project: {
      _id: "$_id",
      title: "$title",
      titleAll: {
        $concat: [
          "$title",
          " > ",
          "$subjects.name",
          
        ]
      },
      
    }
  },
  
])

And this is the output that I get:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "title": "John",
    "titleAll": "John \u003e math"
  },
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "title": "John",
    "titleAll": "John \u003e physics"
  }
]

Now, I would like my output to have first item in array with just the real title, and then listing all of this, like this:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "title": "John",
    "titleAll": "John"
  },
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "title": "John",
    "titleAll": "John \u003e math"
  },
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "title": "John",
    "titleAll": "John \u003e physics"
  }
]

Is there any way to achieve it? I tried using $cond, but does not seem to work. Here is the link to Mongo playground

CodePudding user response：

You can do it in a simpler way:

No need to $unwind and $group again. The missing step was to concat the title to the titleAll array.

db.Students.aggregate([
  {
    $lookup: {
      from: "Subjects",
      localField: "marks_in_subjects.subject_id",
      foreignField: "id",
      as: "subjects"
    }
  },
  {
    $project: {subjects: "$subjects.name", title: 1}
  },
  {
    $addFields: {
      titleAll: {
        $map: {
          input: "$subjects",
          as: "item",
          in: {$concat: ["$title", " > ", "$$item"]}
        }
      }
    }
  },
  {
    $project: {
      title: "$title",
      titleAll: {"$concatArrays": ["$titleAll", ["$title"]]}
    }
  },
  {
    $unwind: "$titleAll"
  }
])

Sample MongoDB Playground

CodePudding user response：

1. $unwind
2. $lookup
3. $group - Group by _id. Get the title field and subjects array (this field is needed in stage 4).
4. $set - Modify subjects field by adding first document and existing documents in subjects.
5. $unwind - Deconstruct subjects field.
6. $project - Decorate output document.

db.Students.aggregate([
  {
    $unwind: "$marks_in_subjects"
  },
  {
    "$lookup": {
      "from": "Subjects",
      "localField": "marks_in_subjects.subject_id",
      "foreignField": "id",
      "as": "subjects"
    }
  },
  {
    $group: {
      _id: "$id",
      title: {
        $first: "$title"
      },
      subjects: {
        $push: {
          $first: "$subjects"
        }
      }
    }
  },
  {
    $set: {
      subjects: {
        $concatArrays: [
          [
            {
              titleAll: "$title"
            }
          ],
          {
            $map: {
              input: "$subjects",
              in: {
                titleAll: {
                  $concat: [
                    "$title",
                    " > ",
                    "$$this.name"
                  ]
                }
              }
            }
          }
        ]
      }
    }
  },
  {
    $unwind: "$subjects"
  },
  {
    $project: {
      _id: 1,
      title: 1,
      titleAll: "$subjects.titleAll"
    }
  }
])

Sample Mongo Playground