MongoDB: How to find documents containing a string in subdocuments in any fields without specifying array index?

Question

I want to find all documents containing the word "school" in ANY of their subdocuments fields, for example, if we have two documents:

{
    name: "aa",
    schools: [
        {
            name: "high school",
            description: "aaaa"
        },
        {
            name: "a1",
            description: "blabla bla school"
        },
        ...
    ],
},
{
    name: "bb",
    schools: [
        {
            name: "bbb",
            description: "First school of ..."
        },
        {
            name: "b school",
            description: "bbb  bb b",
            ...
        }
    ],
},

These documents should match with the name or/and description of the first or/and second school.

I tried this query without success:

{
    schools: /.*school.*/
}

And this too, by listing fields:

{
    school: [
        { name: /.*school.*/ },
        { description: /.*school.*/ },
        ...
    ]
}

Is their a way to perform this?

Answer 1

If you need a true wildcard query, then @a-h's answer regarding a text index is the way to go. But you can also do this with an $or query that checks multiple fields for matches:

db.schools.find({
  $or: [
    { 'schools.name': /school/ },
    { 'schools.description': /school/ },
    ...
  ]
})

Answer 2

The easiest way is to create a full-text index.

You can do this by following the documentation at https://docs.mongodb.org/manual/core/index-text/

The index can then be queried with the $text operator: https://docs.mongodb.org/manual/reference/operator/query/text/

Create Index on All Fields

db.schools.createIndex( { "$**": "text" } )

Search

db.schools.find({
  $text:
    {
      $search: "school",
      $language: "en",
      $caseSensitive: false,
      $diacriticSensitive: false
    }
})