MongoDB search string by first letter of each word in an array field

Question

I have a table in Mongo DB called 'Business Opportunities'(Check below image).

Currently, I am creating node js service where it returns all the matching values for starting letter for business_opportunities field in the table. (highlight one in picture)

So, if i pass 'A' it should only return all the matching ones for letter A in the database but since 'business_opportunities'is an array field it return all the values with matching field like below,

{"status":"success","code":200,"data":{"tags":[{"_id":"5aae9de344fdc45f6a5faf08","business_opportunities":["Web Design","app Design ","Coding","Software Engineer"]},{"_id":"5aae9de344fdc45f6a5faf14","business_opportunities":["audit","Accounting"]},{"_id":"5b86180c44fdc4427245ec71","business_opportunities":["Apps, website, development, programming, php, java, javascript, ruby, react, native","aws cloud services, ","ios, android, "]},{"_id":"5bb70d8c44fdc442dd41b702","business_opportunities":["accounting","quickbooks","xero","cloud accounting","financial","audit","consulting"]},{"_id":"5bbac54944fdc40d9f40f071","business_opportunities":["accommodation","rent","condo"]}]}}

This is my current code,

static async findRelatedTags(opts, params) {
    assert.object(params, 'params')
    assert.string(params.tag, 'params.tag')
    assert.number(params.limit, 'params.limit')

    assert.object(opts, 'opts')
    assert.object(opts.mongo_db, 'opts.mongo_db')

    const { mongo_db: db } = opts

    const query = [
      {
        $match: {
          business_opportunities: { '$regex': '^'+params.tag+'' }
        }
      },
      {
        $project: {
          business_opportunities: '$business_opportunities'
        }
      }
    ]

    const cursor = db.collection('business_opportunities').aggregate([
      ...query
    ]).limit(params.limit)

    return cursor.toArray()
  }

So I want output like this, (if I search 'A')

{
 "status":"success",
 "code":200,
 "data":{
         "tags": [
                   accounting,
                   app design,
                   aws
                  ]
         }
 }

Answer 1

You need to unwind the business_opportunities before matching them with regular expression. Below is the updated code.

static async findRelatedTags(opts, params) {
    assert.object(params, 'params')
    assert.string(params.tag, 'params.tag')
    assert.number(params.limit, 'params.limit')

    assert.object(opts, 'opts')
    assert.object(opts.mongo_db, 'opts.mongo_db')

    const { mongo_db: db } = opts

    const query = [
      { $unwind : "$comments" },
      {
        $match: {
          business_opportunities: { '$regex': '^'+params.tag+'', $options: 'i'  }
        }
      },
      { "$group": {
         "_id": "$comments"
        }
      }
    ]

    const cursor = db.collection('business_opportunities').aggregate([
      ...query
    ]).limit(params.limit)

     var agg_results = cursor.toArray()
     return agg_results.map(obj => obj._id) 
  }

MongoDB aggregation will give you the matched business_opportunities like this

[{
    "_id" : "accounting"
},
{
    "_id" : "app design"
},
{
    "_id" : "aws"
}]

To convert it to ["accounting", "app design", "aws"] I have added agg_results.map(obj => obj._id) in example above.

Except that in your code regex search is Case Sensitive so I have used { '$regex': '^'+params.tag+'', $options: 'i' } to make it Case Insensitive, remove $options if you don't need it.