mongodb search and paginate documents in array

Question

i have product_group collection:

{
  "_id": ObjectId("6769b16dcf07c914fdce1e8c"),
  "product_group_id": 929,
  "sellable_items": [
    {
      "added_date":  "2024-12-23T18:50:21.832Z",
      "id": "47464",
      "menu_product_id": "17619",
      "menu_product_name": "Blueberry Juice Blend",
      "sellable_item_name": "Light Blueberry Juice Blend"
    },
    {
      "added_date": 2024-12-23T18:53:03.970Z",
      "id": "50645",
      "menu_product_id": "20186",
      "menu_product_name": "Mocha",
      "sellable_item_name": "White Mocha"
    },
    {
      "added_date": 2024-12-23T18:52:03.970Z",
      "id": "50637",
      "menu_product_id": "20179",
      "menu_product_name": "Black Coffee",
      "sellable_item_name": "Black Coffee 250 grams"
    }
  ]
}

I need to find a product_group by its product_group_id, then find all its sellable_items which contains 506 in the sellable_items.id or sellable_items.sellable_item_name fields, and return the matched sellable_item, sort and group them.

and here is the aggregation:

[
  {
    $match:
      {
        $and: [
          {
            product_group_id: 929
          },
          {
            $or: [
              {
                "sellable_items.id": {
                  $regex: /506/
                }
              },
              {
                "sellable_items.sellable_item_name":
                  {
                    $regex: /506/
                  }
              }
            ]
          }
        ]
      }
  },
  {
    $unwind:
      {
        path: "$sellable_items"
        // includeArrayIndex: 'string',
        // preserveNullAndEmptyArrays: boolean
      }
  },
  {
    $match:
      {
        $or: [
          {
            "sellable_items.id": {
              $regex: /506/
            }
          },
          {
            "sellable_items.sellable_item_name": {
              $regex: /506/
            }
          }
        ]
      }
  },
  {
    $sort:
      {
        "sellable_items.added_date": -1
      }
  },
  {
    $group:
      {
        _id: "$product_group_id",
        result: {
          $push: {
            items: "$sellable_items"
          }
        }
      }
  },
  {
    $project:
      {
        product_group_id: 1,
        result: 1
      }
  }
]

it return this result:

{
  "_id": "929",
  "result": [
    {
      "items": {
        "added_date": 2024-12-24T04:33:02.675Z",
        "id": "50637",
        "menu_product_id": "20179",
        "menu_product_name": "Black Coffee",
        "sellable_item_name": "Black Coffee 250 grams"
      }
    },
    {
      "items": {
        "added_date":"2024-12-24T04:31:34.607Z",
        "id": "50645",
        "menu_product_id": "20186",
        "menu_product_name": "Mocha",
        "sellable_item_name": "White Mocha"
      }
    }
  ]
}

problem with this is: i want to do text search on sellable_item id and name, but when i changed first $match to

{ $and: [ { product_group_id: 929 }, { $text:{$search: "506"} } ] }

it returned empty result. I have create index on sellable_items.id as text type.

thanks for the help

Answer 1

If you need to "return the matched sellable_item, sort and paginate them" it really should be a collection of sellable_item documents with product_group_id as a field.

At least if you want to use a fulltext search.

Both $search and $text require an index built on document insertion/modification, i.e. the index is built before you run the aggregation pipeline and refers to whole documents. This is why they have some restrictions. In particular The $match stage that includes a $text must be the first stage in the pipeline

Mongodb is document-oriented database, and even though it allows subdocuments, many operations work on document level only.

So, if you have flexibility to change schema and use a collection with sellable_items as individual documents you will be able to benefit from text indices. Otherwise the regexps will remain your only option.