Only return hits in array from mongodb

user_4551212 0
Viewed 88

So I have a mongodb collection with some nested documents. An example document looks like this:

{
  "_id": "5afa9472e937b7254a306ff6",
  "import_date": "2018-05-15T08:04:02.813Z",
  "some_more_things": "foo",
  "meta": {
    "participants": [{ "name": "Ben" }, { "name": "Mary" }],
    "messages": [
      {
        "tokens": [
          { "token": "What" },
          { "token": "do" },
          { "token": "you" },
          { "token": "do" },
          { "token": "today" }
        ],
        "time": "2018-05-09T08:38:19.000Z"
      },
      {
        "tokens": [
          { "token": "Just" },
          { "token": "lying" },
          { "token": "around" }
        ],
        "time": "2018-05-09T08:40:08.000Z"
      },
      {
        "tokens": [
          { "token": "What" },
          { "token": "about" },
          { "token": "you" }
        ],
        "time": "2018-05-09T08:40:11.000Z"
      }
    ]
  }
}

I'm now looking for an effective way to search for messages where a specific token is included. I'm doing this with following query:

db.conversations.find({'meta.messages.tokens.token': /^What$/i})
   .projection({'import_date': 1, 'meta.messages': 1})
   .sort({_id:-1})
   .limit(100)

That way I find the docs I want but I get the complete messages array. Is there a way that I can get only the items of the messages array matching with my regexp? The result should look like this (so only the first and last item of my example doc).

{
  "_id": "5afa9472e937b7254a306ff6",
  "import_date": "2018-05-15T08:04:02.813Z",
  "meta": {
    "participants": [{ "name": "Ben" }, { "name": "Mary" }],
    "messages": [
      {
        "tokens": [
          { "token": "What" },
          { "token": "do" },
          { "token": "you" },
          { "token": "do" },
          { "token": "today" }
        ],
        "time": "2018-05-09T08:38:19.000Z"
      },
      {
        "tokens": [
          { "token": "What" },
          { "token": "about" },
          { "token": "you" }
        ],
        "time": "2018-05-09T08:40:11.000Z"
      }
    ]
  }
}
aggregation-framework mongodb
1 Answers

You can use $indexOfBytes to check if What exists in every string. You also need $map with $filter and $anyElementTrue to build your filtering condition for nested array:

db.collection.aggregate([
    {
        $addFields: {
            "meta.messages": {
                $filter: {
                    input: "$meta.messages",
                    as: "m",
                    cond: {
                        $anyElementTrue: {
                            $map: {
                                input: "$$m.tokens",
                                in: { $gte: [ { $indexOfBytes: [ "$$this.token", "What" ] }, 0 ] }
                            }
                        }
                    }
                }
            }
        }
    }
])

Mongo Playground

If you need Regex you can take a look at $regexMatch introduced in 4.2 and use it as replacement for $indexOfBytes

user_62389770
Related