mongodb search through embedded array

Question

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,}

Your schema needs to change i think, searchable should be an array, as it is now, you can try $objectToArray and then search wth $filter for example, but i dont think its worth it, yu need schema change. — Takis
– Takis, Commented Aug 15, 2022 at 1:53
You can search the array field using the $filter array operator - use the $regexMatch to find if any of the array element's search_text field has the text you are looking for. The resulting array after applying the $filter will give you the number of matching searcheable array sub-documents. — prasad_
– prasad_, Commented Aug 15, 2022 at 5:17

Tom Slabbaert · Accepted Answer · 2022-08-15 09:14:03Z

1

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

edited Aug 15, 2022 at 9:14

answered Aug 15, 2022 at 5:58

Tom Slabbaert

22.5k10 gold badges34 silver badges45 bronze badges

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

4 Comments

Brett Over a year ago

if they all contain the search query I only get back the first one returned. That aside, is there a better way I could lay this out in Mongodb to achieve what I am after. Perhaps if I had '_id' from the parent, in each of the searchable arrays, My thinking was, if I find it in a 'searchable' index, from there I would have the _id ?

Tom Slabbaert Over a year ago

this was quite easy to change, i only project the first match because of the sample you gave the output was not an array. maybe you change your question to have the actual output you want - it will be easier to answer it if i know what you're looking for.

Brett Over a year ago

thanks, have updated the desired output, in the question

Tom Slabbaert Over a year ago

it's exactly what i commented in the previous comment - mongoplayground.net/p/CaUF1IfrErg -. just remove the wrapping "$arrayElemAt".

Collectives™ on Stack Overflow

mongodb search through embedded array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related