MongoDB: Ignore documents with array size null in aggregation query

Question

I have a variable roomsCount which is a filter for UI list of projects by amount of rooms. In the database, projects have value rooms which is an array, and store all data about them. This function sorts them, but here I also need to exclude projects where are no rooms or that field doesn't exist. I have found a way how to do it if there is not an aggregation, but how to avoid them in the pipeline?

  async aggregateAll(page, pageSize, filters, sortBy) {
    const options = {
      query: new FiltersQuery(filters),
      select: this.getAttributes(),
      sort: this.parseSortBy(sortBy),
      skip: (page - 1) * pageSize,
      limit: pageSize,
    };

    const query = this.projectModel.aggregate([
      { $match: options.query },
      {
        $addFields: { roomsCount: { $size: { $ifNull: ['$rooms', []] } } },
      },
      { $sort: { roomsCount: -1 } },
      { $skip: options.skip },
      { $limit: options.limit },
      { $project: options.select },
    ]);

Sergi Nadal · Accepted Answer · 2021-02-26 13:44:52Z

1

I think you should split the query.

First, check if field room exists with $exists.

$match: {room:{$exists:true}}

Then check the size, I think for check the size you will need to use $addFields, because with $size you can't use $gt.

$addFields: { roomSize: {$size:"$room"}},
$match: {roomSize:{$gt:0}}

answered Feb 26, 2021 at 13:44

Sergi Nadal

96014 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

not_a_bob Over a year ago

May I add several $match in an aggregate query?

Sergi Nadal Over a year ago

yes, you should remove your $addFields, and add my "solution"

Collectives™ on Stack Overflow

MongoDB: Ignore documents with array size null in aggregation query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related