mongodb search through embedded array-CodePudding

I have a Mongodb doc structure, with many docs similar to this.

{   '_id': ObjectID(62f8199dc1e8c0f11820cb91)
    'name': 'foo',
    'score': 4500,
    'searchable': [
        {
            'title': 'blah',
            'date': 'some_date',
            'search_text': "Blah blah blah ...."

        },
        {
            'title': 'bleep',
            'date': 'some_date',
            'search_text': "Lorem Lorem Lorem ...."

        },
        {
            'title': 'bloop',
            'date': 'some_date',
            'search_text': "Ipsum Ipsum Ipsum ...."

        }]
},

I have been trying to search for a specific string in any of the 'searchable' array objects 'search_text' fields, but filtered by 'score' (min and max). I need to return which object in 'searchable'that the string was found in, and in which document that object belongs to...

I assumed it was a job for aggregation, and $unwind but have not managed to produce a workable result. Help greatly appreciated.

using the example document above, if I searched for

"Lorem, with 'scores' between 3000 and 5000"

I want to get back that it appears in documentID: '62f8199dc1e8c0f11820cb91', 'searchable': {1}'

if more than one 'searchable' in the document matches, then return them as well, like: documentID: '62f8199dc1e8c0f11820cb91', 'searchable': {1, 2, 5,}

CodePudding user response：

It's not the sexiest pipeline but here is an example:

(The syntax could be much cleaner if we could return the actual match instead of the matching index of the element)

const input = {
   text: "Lorem",
   minScore: 3000,
   maxScore: 5000
};

db.collection.aggregate([
  {
    $match: {
      "searchable.search_text": {
        $regex: input.text
      },
      score: {
        $gte: input.minScore,
        $lte: input.maxScore
      }
    }
  },
  {
    $project: {
      documentID: "$_id",
      _id: 0,
      searchable: {
            $map: {
              input: {
                $filter: {
                  input: {
                    "$map": {
                      "input": {
                        $zip: {
                          inputs: [
                            "$searchable",
                            {
                              "$range": [
                                0,
                                {
                                  $size: "$searchable"
                                }
                              ]
                            }
                          ]
                        }
                      },
                      in: {
                        "$mergeObjects": [
                          {
                            "$arrayElemAt": [
                              "$$this",
                              0
                            ]
                          },
                          {
                            index: {
                              "$arrayElemAt": [
                                "$$this",
                                1
                              ]
                            }
                          }
                        ]
                      }
                    }
                  },
                  cond: {
                    "$regexMatch": {
                      "input": "$$this.search_text",
                      "regex": input.text
                    }
                  }
                }
              },
              in: "$$this.index"
            }
          }
    }
  }
])

Mongo Playground