How to check all the collections in a MongoDB database for a particular keyword?

Question

I am currently working on a NodeJS project using Mongoose queries.

A new requirement has come up which I am unable to understand how to tackle. I want to take a query string and search for this in all the collections of my database to return the objects that match this value.

This is how my collection looks:

Books:

{
    "book_id": "ab12nld”,
    "book_version": "0”,
    "author": “Sam”,
    “name”: “Sample Book”,
    “comments”: “Done”
  },
  {
    "book_id": "a5g2nld”,
    "book_version": "1",
    "author": "Martin",
    "name": "Sample Book",
    “comments”: “In Progress”
  }

Cars:

{
    "car_id": "nlp240n”,
    "car_model": "3”,
    "brand": “Hyundai”,
    “name”: “Verna”,
    “color”: “Blue”
  },
  {
    "car_id": "cak201n”,
    "car_model": "1”,
    "brand": “Tata”,
    “name”: “Indica”,
    “color”: “Black”
  }

Phones:

{
    "phone_id": "n0830bo”,
    "brand": “OnePlus”,
    “name”: “7 Pro”,
    “color”: “Red”
  },
  {
    "phone_id": "bh97b01”,
    "brand": “IPhone”,
    “name”: “11 Pro”,
    “color”: “Black”
  }

I will get a query_string which will be searched for in this collection and if there is a matching result, I should get all the related values.

Example:

If the query_string is Martin, I should get the corresponding result from the Books collection as Martin is present only there:

 {
    "book_id": "a5g2nld”,
    "book_version": "1",
    "author": "Martin",
    "name": "Sample Book",
    “comments”: “In Progress”
  }

If the query_string is Black, I should get the corresponding result from both Cars and Phones collections, as both these have the value Black:

{
    "car_id": "cak201n”,
    "car_model": "1”,
    "brand": “Tata”,
    “name”: “Indica”,
    “color”: “Black”
  },
{
    "phone_id": "bh97b01”,
    "brand": “IPhone”,
    “name”: “11 Pro”,
    “color”: “Black”
  }

This seems tricky and I don't know how to proceed forward. Any help will be appreciated.

Try `$lookup` in mongodb .-https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/ — Shubham Dixit, Oct 31 '19 at 09:25
Have a look here as well -https://stackoverflow.com/questions/37086387/multiple-join-conditions-using-the-lookup-operator — Shubham Dixit, Oct 31 '19 at 09:26
@Shubh `$lookup` requires that there a "join" field in two collections. In this case there is no such field, so, `$lookup` may not work. — prasad_, Oct 31 '19 at 09:49

prasad_ · Answer 1 · 2019-11-08T09:25:22.973

Here are couple of options. These use basic querying and JavaScript with different levels of complexity:

OPTION #1:

Query on all the fields you want to search for each collection:

var booksFound = db.books.find( { $or [ { author: <querystring>} , { name: <querystring> } ]  } ).toArray()
var carsFound = db.cars.find( { $or [ { brand: <querystring> }, { name: <querystring> }, { color: <querystring> } ] } ).toArray()
var phonesFound = db.phones.find( { $or [ { brand: <querystring> }, { name: <querystring> }, { color: <querystring> } ] } ).toArray()

Merge the three result arrays using the JavaScript Arrays#concat method to get the result:

var resultArray = booksFound.concat(carsFound).concat(phonesFound);

Indexes and Collation: Note that you have to create indexes on the respective fields in each collection for a faster search. You can also add a case-insensitive option (for case-insensitive search) to the index using collation: { collation: { locale: 'en', strength: 2 } }. See the note on Case Insensitive Indexes.

OPTION #2:

Create a new keywords field, and it will be an array of values from the fields you want to search on. You can name the field as "keywords" or "searchwords".

For example, the cars collection's document with car_id: "nlp240n", you can have the keywords field as keywords: [ "Hyundai", "Verna", "Blue"]

You need to create an index on this array field for faster querying. Indexes on arrays are called multi-key indexes. You can also add a case-insensitive option to the index using collation. See OPTION#1 for more details about collation.

NOTE: The keywords field and the indexes needs to be created on the three collections.

Make three queries and get results from each query as an array:

var booksFound = db.books.find( { keywords: <querystring> } ).toArray();
var carsFound = db.cars.find( { keywords: <querystring> } ).toArray();
var phonesFound = db.phones.find( { keywords: <querystring> } ).toArray();

Merge the three arrays using the JavaScript Arrays#concat method to get the result:

var resultArray = booksFound.concat(carsFound).concat(phonesFound);

How to create keywords field data?

Each time a document is inserted, updated or deleted (and if any of the fields we are querying on are) from the collection the keywords array needs to be updated accordingly. On insert add the value to the array, on update change the value in the array and on delete remove the value in the array

See this for more info: Array Update Operators.

Haruo · Accepted Answer · 2019-10-31T12:17:02.287

In Your case, you can do this way:

var searchValue = "Martin";
// FOR EACH COLLECTION IN MONGODB...
// HERE YOU COULD ITERAVE OVER AN ARRAY OF COLLECTIONS NAMES...
// var collections = ["Books", "Cars", "Phones"];
// collections.forEach(
db.getCollectionNames().forEach(
    function(collectionName) {
        // GETTING THE FIELD NAMES FOR THE COLLECTION...
        // FOR COLLECTIONS WITH MANY DOCUMENTS IT MAY BE MORE INTERESTING TO GET THE FIELD NAMES ELSEWHERE INSTEAD OF MONGODB...
        db.getCollection(collectionName).aggregate([
          {"$project":{"arrayOfKeyValue":{"$objectToArray":"$$ROOT"}}},
          {"$unwind":"$arrayOfKeyValue"},
          {"$group":{"_id":null,"allFields":{"$addToSet":"$arrayOfKeyValue.k"}}},
          {"$project":{"collectionName": collectionName, "fieldNames": "$allFields"}}
        ]).forEach(
            function(collectionWithFieldNames) {  
                // FOR EACH FIELD NAME OF THE COLLECTION...
                collectionWithFieldNames.fieldNames.forEach(
                    function(fieldName) {
                        var name = fieldName;
                        var value = searchValue;
                        var query = {};
                        query[name] = value;

                        // QUERY THE COLLECTION SEARCHING THE SEARCH VALUE ON THE FIELD IN THE COLLECTION...
                        db.getCollection(collectionName).find(query).forEach(
                            function(result) {
                                // PRINT THE RESULT IF EXISTS...
                                // HERE YOU CAN DO EVERYTHING WITH THE RESULT...
                                print(JSON.stringify(result));
                            }
                        );
                    }
                );
            }
        )
    }
);

How to check all the collections in a MongoDB database for a particular keyword?

2 Answers2