I have a problem with a query that I can't solve 100%

The fact is that when a user does not have any comment within the post. As inside the comments there is a "createdBy" and I need to make a lookup of that user inside the array. If there are no comments, it returns an array with an empty object, but it must return an empty array, not with empty objects.

Who can help me? Thank you very much in advance!

HERE MY USER collection (data)

[
  {
    _id: ObjectId("619d0f5df3f74665aff1a551"),
    name: "Test Name",
    surname: "Test Surname2",
    createdAt: ISODate("2021-11-11T17:21:58.624 01:00"),
    updatedAt: ISODate("2021-11-25T10:35:25.842 01:00"),
    posts: [
      {
        _id: ObjectId("619d0f5df3f74575aff1a551"),
        updatedAt: ISODate("2021-11-23T16:57:17.816 01:00"),
        createdAt: ISODate("2021-11-23T16:57:17.816 01:00"),
        content: "Test content....",
        comments: [
          {
            createdBy: ObjectId("618d4326f1668007b3b98404"),
            comment: "test comment...",
            _id: ObjectId("619dfaaaa88266dc91b9489c"),
          },
          {
            createdBy: ObjectId("618d4326f1668007b3b98404"),
            comment: "test comment...",
            _id: ObjectId("619dfc60a88266dc91b95741"),
          },
        ],
        date: ISODate("2021-11-23T16:57:17.820 01:00"),
      },
      {
        _id: ObjectId("619d0f5df3f74575aff1a551"),
        updatedAt: ISODate("2021-11-23T16:57:17.816 01:00"),
        createdAt: ISODate("2021-11-23T16:57:17.816 01:00"),
        content: "Test content....",
        comments: [],
        date: ISODate("2021-11-23T16:57:17.820 01:00"),
      },
    ],
  },
  {
    _id: ObjectId("619d0f5df3f74665aff1a551"),
    name: "Test Name",
    surname: "test surname",
    createdAt: ISODate("2021-11-11T17:21:58.624 01:00"),
    updatedAt: ISODate("2021-11-25T10:35:25.842 01:00"),
    posts: [
      {
        _id: ObjectId("619d0f5df3f74575aff1a551"),
        updatedAt: ISODate("2021-11-23T16:57:17.816 01:00"),
        createdAt: ISODate("2021-11-23T16:57:17.816 01:00"),
        content: "Test content....",
        comments: [
          {
            createdBy: ObjectId("618d4326f1668007b3b98404"),
            comment: "test comment...",
            _id: ObjectId("619dfaaaa88266dc91b9489c"),
          },
          {
            createdBy: ObjectId("618d4326f1668007b3b98404"),
            comment: "test comment...",
            _id: ObjectId("619dfe7ba88266dc91b961b6"),
          },
        ],
        date: ISODate("2021-11-23T16:57:17.820 01:00"),
      },
      {
        _id: ObjectId("619d0f5df3f74575aff1a551"),
        updatedAt: ISODate("2021-11-23T16:57:17.816 01:00"),
        createdAt: ISODate("2021-11-23T16:57:17.816 01:00"),
        content: "Test content....",
        comments: [
          {
            createdBy: ObjectId("618d4326f1668007b3b98404"),
            comment: "test comment...",
            _id: ObjectId("619dfaaaa88266dc91b9489c"),
          },
          {
            createdBy: ObjectId("618d4326f1668007b3b98404"),
            comment: "test comment...",
            _id: ObjectId("619dfc60a88266dc91b95741"),
          },
        ],
        date: ISODate("2021-11-23T16:57:17.820 01:00"),
      },
    ],
  },
];

HERE MY AGGREGATE QUERY

db.users.aggregate([
    { $unwind: { path: '$posts', preserveNullAndEmptyArrays: true } },
    { $unwind: { path: '$posts.comments', preserveNullAndEmptyArrays: true } },
    {
        $lookup: {
            from: 'users',
            localField: 'posts.comments.createdBy',
            foreignField: '_id',
            as: 'posts.comments.createdBy'
        }
    },
    { $unwind: { path: '$posts.comments.createdBy', preserveNullAndEmptyArrays: true } },
    {
        $group: {
            _id: { _id: '$_id', post_id: '$posts._id' },
            name: { $first: '$name' },
            posts: { $push: '$posts' },
            comments: { $push: '$posts.comments' },
        }
    },
    {
        $group: {
            _id: '$_id._id',
            name: { $first: '$name' },
            posts: {
                $push: {
                    _id: '$_id.post_id',
                    date: { $first: '$posts.date' },
                    content: { $first: '$posts.content' },
                    comments: '$comments'
                }
            }
        }
    },
])

Here an image with the fail array:

CodePudding user response：

you have to remove preserveNullAndEmptyArrays field from unwind to don't have empity objects

CodePudding user response：

Check Marco: createdBy is ObjectId. but it still doesn't work correctly