How to use full text search in referenced documents?

Question

I know this was asked before, but I can't find an answer that works for me.

I have some documents, which have reference to another document, like users and orders:

Users model:

import mongoose from '../database/index.js';
import mongoosePaginate from 'mongoose-paginate-v2';

const UsersSchema = new mongoose.Schema({
    user_id: {
        type: String,
        required: true,
    },
    email: {
        type: String,
        required: true,
        unique: true,
    },
    firstName: {
        type: String,
        default: '',
    },
    lastName: {
        type: String,
        default: '',
    },
    orders: [
        {
            type: mongoose.Schema.Types.ObjectId,
            ref: 'Orders',
        },
    ],
    recipients: [
        {
            type: mongoose.Schema.Types.ObjectId,
            ref: 'Recipients',
        },
    ],
    createdAt: {
        type: Date,
        default: Date.now,
        required: true,
        select: false,
    },
});

UsersSchema.index({ email: 'text', firstName: 'text', lastName: 'text' });

UsersSchema.plugin(mongoosePaginate);

UsersSchema.set('toJSON', {
    virtuals: true,
});

const Users = mongoose.model('Users', UsersSchema);

export default Users;

Orders model:

import mongoose from '../database/index.js';
import mongoosePaginate from 'mongoose-paginate-v2';
import Users from './users.js';

import OrderStatus from '../enums/OrderStatusEnum.js';

const OrdersSchema = new mongoose.Schema({
    user: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'Users',
        required: true,
    },
    total: {
        type: Number,
        required: true,
    },
    status: {
        type: String,
        enum: OrderStatus.values(),
        required: true,
        default: OrderStatus.CREATED,
    },
    payment: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'PaymentMethods',
        required: true,
    },
    shortId: {
        type: String,
        required: true,
    },
    createdAt: {
        type: Date,
        default: Date.now,
        required: true,
    },
});

OrdersSchema.index({ shortId: 'text' });

OrdersSchema.plugin(mongoosePaginate);

OrdersSchema.pre('remove', function (next) {
    Users.update({ orders: this._id }, { $pull: { orders: this._id } }).exec();
    next();
});

OrdersSchema.set('toJSON', {
    virtuals: true,
});

const Orders = mongoose.model('Orders', OrdersSchema);

export default Orders;

I can use the $text search to query full text search from orders or users:

    const orders = await Orders.paginate(
        { $text: { $search: query.filter.q } },
        query.options
    );
    return orders;

But this will only make a search on the orders index. If, i.e., I would like to search for the order of the user whose first name is Joe, how can I make it also search in the user.firstName field, since this is a reference from Orders to Users?

I know I can't populate the fields and then search on all model, but I'm not sure how to achieve what I'm looking for.

Thanks in advance

user_9540925 · Answer

Because full text search query must be the first stage in the aggregation pipeline, it is not currently possible to perform full text search in two collections as part of the same query.

You'll need to reorganize your data.

user_12816049 · Answer

my requirement was to find orders of users matched with their names, partial or full-text search. the user id is ref into orders doc.

from mongodb playground: https://mongoplayground.net/p/ASOSFvfURXW

db.inventory.aggregate([
  {
    "$lookup": {
      "from": "orders",
      "localField": "orderId",
      "foreignField": "_id",
      "as": "order_docs"
    }
  },
  {
    "$unwind": "$order_docs"
  },
  {
    "$match": {
      "order_docs.item": {
        "$regex": "pec",
        "$options": "i"
      }
    }
  }
])

How to use full text search in referenced documents?

2 Answers