Trying to filter sub document in aggregation

Working with Data
,
1

Trying to filter sub document in aggregation but in response not getting parent data if sub document data is empty getting only array.

const data = await item.aggregate([
  {
    $match: {
      _id: new Types.ObjectId(id),
    },
  },
  {
    $lookup: {
      from: "posts",
      localField: "posts.post",
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: ["$status", 'active'],
                },
                {
                  $lt: ["$expiry_time", new Date()],
                },
              ],
            },
          },
        },
      ],
      foreignField: "_id",
      as: "posts.post",
    },
  },
  {
    $unwind: "$posts.post",
  },
  {
    $group: {
      _id: "$_id",
      name: { $first: "$name" },
      posts: {
        $push: {
          _id: "$posts.post._id",
          caption: "$posts.post.caption",
      },
    },
  },
},
  {
    $project: {
      name: 1,
      posts: {
            $slice: ['$posts', (+page - 1) * 20, 20],
          },
    },
  },
]);

if the condition satisfy any post then I get parent data other wise I get an empty array.

2

It seems to me that this is what your aggregation pipeline is asking, “Give me all documents where I can find a record in the posts collection with the the foreign key I’m providing from the item collection.”

3

so how can I fix this?

4

I’m not sure what you’re trying to do?

2 Likes
5

Nobody is. I am confused too. Providing sample documents from your collections and expected results would go a long way to make us understand.

6

Sorry for that, I thought I have put the schema

 const item = new Schema(
  {
    name: {
      type: String,
      required: true,
      trim: true,
    },
    posts: [
      {
        post: {
          type: Schema.Types.ObjectId,
          ref: 'Post',
        },
      },
    ],
  },
);

this is Item schema

In the above searching, I am getting data when both conditions are match with any document, I want to get parent document data when “post” condition does not match with any condition.

Like this

{
  name: 'Lorem',
  post: []
}

but I am getting an empty

{}

without name or any data.

7

I asked for documents not schema. We need real documents to experiment. We could create documents from your schema but it is time consuming. It is much more efficient if you share your documents.

8

Well, I think I see the point, @steevej … How does one structure an aggregation including a lookup so that one returns the original match document even if the lookup result is an empty set?

9

Item document

{
  "_id": {
    "$oid": "64d1377633b2c02fd6eaa1b3"
  },
  "name": "darkness",
  "__v": 0,
  "createdAt": {
    "$date": "2023-08-07T18:27:02.171Z"
  },
  "posts": [
    {
      "post": {
        "$oid": "65992354be6539c604de760a"
      },
      "_id": {
        "$oid": "65992354be6539c604de760d"
      }
    },
    {
      "post": {
        "$oid": "659edd406d831d0eda4a6b6f"
      },
      "_id": {
        "$oid": "659edd406d831d0eda4a6b72"
      }
    }
  ],
  "updatedAt": {
    "$date": "2024-01-10T18:09:04.439Z"
  }
}

Post Schema

const post = new Schema(
  {
    caption: { type: String },
    expiry_time: {
      type: Date,
      default: function () {
        const expiryDate = new Date();
        expiryDate.setDate(expiryDate.getDate() + 1);
        return expiryDate;
      },
    },
  })

Post Document


{
  "_id": {
    "$oid": "65992354be6539c604de760a"
  },
  "caption": "abc",
  "expiry_time": {
    "$date": "2024-01-07T09:54:38.450Z"
  },
  "createdAt": {
    "$date": "2024-01-06T09:54:28.450Z"
  },
  "updatedAt": {
    "$date": "2024-01-06T15:55:16.314Z"
  },
  "__v": 0
}

{
  "_id": {
    "$oid": "659edd406d831d0eda4a6b6f"
  },
  "caption": "power",
  "expiry_time": {
    "$date": "2024-01-11T18:09:14.414Z"
  },
  "createdAt": {
    "$date": "2024-01-10T18:09:04.418Z"
  },
  "updatedAt": {
    "$date": "2024-01-11T17:10:00.367Z"
  },
  "__v": 0
}
10

yes return the parent document data, if the lookup data not found or lookup data is empty array or null

11

@Golu_Rajak maybe the problem is in your last $project clause:

{
    $project: {
      name: 1,
      posts: {
            $slice: ['$posts', (+page - 1) * 20, 20],
          },
    },
  }

It seems to me that whatever happens, you’re not projecting anything from the item collection

1 Like
12

Hi @Golu_Rajak

Looking at your example and your aggregation, I’d remove the grouping and unwind. I say this because your grouping is essentially transforming the result, which can be done in the pipeline of the lookup, then you just need to project the property back to an array. Below is the aggregation rewritten (slightly) for mongocompass:

[
  {
    $match: {
      _id: ObjectId('64d1377633b2c02fd6eaa1b3'),
    },
  },
  {
    $lookup: {
      from: "posts",
      localField: "posts.post",
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: ["$status", "active"],
                },
                {
                  $lt: [
                    "$expiry_time",
                    new Date(),
                  ],
                },
              ],
            },
          },
        },
        {
          $project: {
            _id: 1,
            caption: 1,
          },
        },
      ],
      foreignField: "_id",
      as: "posts.post",
    },
  },
  {
    $project: {
      name: 1,
      posts: {
            $slice: ['$posts.post', 0, 20],
          },
    },
  }
]

Using your sample data this gives the result:

{
  "_id": {
    "$oid": "64d1377633b2c02fd6eaa1b3"
  },
  "name": "darkness",
  "posts": []
}

Which I believe is what your are after. Adding the status to active on one of you post documents you will get the following:

{
  "_id": {
    "$oid": "64d1377633b2c02fd6eaa1b3"
  },
  "name": "darkness",
  "posts": [
    {
      "_id": {
        "$oid": "659edd406d831d0eda4a6b6f"
      },
      "caption": "power"
    }
  ]
}

Hope that helps.

Craig.

13

@Craig_Crevola Thank you so much

14

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