Mongoose sub field aggregation with full text search and project

Question

I have a Mongoose model called Session with a field named course (Course model) and I want to perform full text search on sessions with full text search, also I wanna aggregate results using fields from course sub field and to select some fields like course, date, etc. I tried the following:

Session.aggregate(
        [
            {
                $match: { $text: { $search: 'web' } }
            },
            { $unwind: '$course' },
            {
                $project: {
                    course: '$course',
                    date: '$date',
                    address: '$address',
                    available: '$available'
                }
            },
            {
                $group: {
                    _id: { title: '$course.title', category: '$course.courseCategory', language: '$course.language' }
                }
            }
        ],
        function(err, result) {
            if (err) {
                console.error(err);
            } else {
                Session.deepPopulate(result, 'course course.trainer 
                  course.courseCategory', function(err, sessions) {
                  res.json(sessions);
            });
            }
        }
    );

My models:

Session

    schema = new mongoose.Schema(
        {
            date: {
                type: Date,
                required: true
            },
            course: {
                type: mongoose.Schema.Types.ObjectId,
                ref: 'course',
                required: true
            },
            palnning: {
                type: [Schedule]
            },
            attachments: {
                type: [Attachment]
            },
            topics: {
                type: [Topic]
            },
            trainer: {
                type: mongoose.Schema.Types.ObjectId,
                ref: 'trainer'
            },
            trainingCompany: {
                type: mongoose.Schema.Types.ObjectId,
                ref: 'training-company'
            },
            address: {
                type: Address
            },
            quizzes: {
                type: [mongoose.Schema.Types.ObjectId],
                ref: 'quiz'
            },
            path: {
                type: String
            },
            limitPlaces: {
                type: Number
            },
            status: {
                type: String
            },
            available: {
                type: Boolean,
                default: true
            },
            createdAt: {
                type: Date,
                default: new Date()
            },
            updatedAt: {
                type: Date
            }
        },
        {
            versionKey: false
        }
    );

Course

let schema = new mongoose.Schema(
    {
        title: {
            type: String,
            required: true
        },
        description: {
            type: String
        },
        shortDescription: {
            type: String
        },
        duration: {
            type: Duration
        },
        slug: {
            type: String
        },
        slugs: {
            type: [String]
        },
        program: {
            content: {
                type: String
            },
            file: {
                type: String
            }
        },
        audience: [String],
        requirements: [String],
        language: {
            type: String,
            enum: languages
        },
        price: {
            type: Number
        },
        sections: [Section],
        attachments: {
            type: [Attachment]
        },
        tags: [String],
        courseCategory: {
            type: mongoose.Schema.Types.ObjectId,
            ref: 'course-category',
            required: true
        },
        trainer: {
            type: mongoose.Schema.Types.ObjectId,
            ref: 'trainer'
        },
        trainingCompany: {
            type: mongoose.Schema.Types.ObjectId,
            ref: 'training-company'
        },
        status: {
            type: String,
            default: 'draft',
            enum: courseStatus
        },
        path: {
            type: String
        },
        cover: {
            type: String,
            required: true
        },
        duration: {
            type: Number,
            min: 1
        },
        createdAt: {
            type: Date,
            default: Date.now
        },
        updatedAt: {
            type: Date
        }
    },
    { versionKey: false }
);

I am not sure if what I tried is gonna bring me what I want and I am getting this error concerning the $unwind operator:

MongoError: exception: Value at end of $unwind field path '$course' must be an Array, but is a OID

Any kind of help will be really appreciated.

You are missing `$lookup` required to pull course document by joining on course object id from session document to id in the course document. Insert the lookup stage between `$match` and `$unwind` stages. Something like `{ $lookup: { from: "course", localField: "course", foreignField: "_id", as: "course" } }`. Adjust to use correct collection name, local field and foreign field. — s7vr, Oct 17 '17 at 17:17
@Veeram I got this error: `MongoError: exception: Unrecognized pipeline stage name: '$lookup'` — jemlifathi, Oct 17 '17 at 17:31
`$lookup` stage was added in 3.2 mongo server. Looks like you are not on 3.2 version. Go to mongo shell and run `db.version()` to verify. — s7vr, Oct 17 '17 at 17:39
This post should help https://stackoverflow.com/questions/39912017/mongodb-aggregate-query-with-lookup — s7vr, Oct 17 '17 at 18:00
@Veeram I migrated to version 3.4.2, It seems to be not working as all I get is an empty array every time — jemlifathi, Oct 18 '17 at 10:01
Your schema looks good to me. What is your collection containing course documents called ? course or courses ? — s7vr, Oct 18 '17 at 11:41
Try `{ $lookup: { from: "courses", localField: "course", foreignField: "_id", as: "course" } }` — s7vr, Oct 18 '17 at 12:15
It works, I mean I am getting an output but it's not what I expected: `[ { "_id": { "title": "Get Started with Angular", "category": "59ce2092aa43630a2eb25f90" } } ]` — jemlifathi, Oct 18 '17 at 12:30
I want it to give me the **course**, the **date**, **address** and **availability** of the session. Is that possible? — jemlifathi, Oct 18 '17 at 12:31

score 2 · Accepted Answer · answered Oct 18 '17 at 12:47

2

You can try below aggregation.

You are missing $lookup required to pull course document by joining on course object id from session document to id in the course document.

$project stage to keep the desired fields in the output.

Session.aggregate([
  {
    "$match": {
      "$text": {
        "$search": "web"
      }
    }
  },
  {
    "$lookup": {
      "from": "courses",
      "localField": "course",
      "foreignField": "_id",
      "as": "course"
    }
  },
  {
    "$project": {
      "course": 1,
      "date": 1,
      "address": 1,
      "available": 1
    }
  }
])

Course is an array with one course document. You can use the $arrayElemAt to project the document.

 "course": {"$arrayElemAt":["$course", 0]}

answered Oct 18 '17 at 12:47

s7vr

73,656
11
106
127

It's working fine excepting when adding a **$match** even with a true condition like for example: ` { $match: { 'available': true } } ` – jemlifathi Oct 18 '17 at 12:59
Are you implying it is not working when you add "available" to match stage ? Something like `{ "$match": { "$text": { "$search": "web" }, "available": true } }` should work. – s7vr Oct 18 '17 at 13:08
No, even only `{ "$match": { "available": true } }` is not working – jemlifathi Oct 18 '17 at 13:09
Actually, even in the output I am not getting **available** property – jemlifathi Oct 18 '17 at 13:11
Its hard to speculate. Just check your documents and make sure it matches what you are looking for. May be it is string "true" value. If the issue persists please consider creating a separate question with all the details. – s7vr Oct 18 '17 at 13:16
I find it out, it's my fault: available set to be true by default, but not available in the database – jemlifathi Oct 18 '17 at 13:21

Mongoose sub field aggregation with full text search and project

1 Answers1