Question

0

184

Views

MongoDB aggregation, nodejs generate dynamic query with AND & OR

I have query with aggregation for MongoDB in my nodejs code.

const query = {
  '$expr':{
    '$and':[
      {'$eq': ['$appId', req.user.appId]},
    ]
  }
}

From the frontend, a filter object is received, as follows:

{
  shops: '604c776763c302032e610347',
  offset: '0',
  limit: '20',
}

I need dynamically push shops id in my query. need to be appId -> user.appID AND targetFrom || targetTo <- shops(from the filter object)

shops can be one id or array of ids, for this I'm using $in operator

query['$expr']['$and'].push({
  $eq: [
    {
      $or: [
        {"targetFrom": {$in: bb}},
        {"targetTo": {$in: bb}}
      ]
    }
  ]
})

The old version of code was as follows:

const query = {
  $and: [
    {appId: req.user.appId}
  ]
}

query['$and'].push({
  $or: [
    {"targetFrom": {$in: bb}}, <- in bb is mongodb objectID
    {"targetTo": {$in: bb}}
  ]
})

Update

How I can search some item by ID in my items array of objects?

if(req.query.products) {
  typeof req.query.products === 'string' ? req.query.products = [req.query.products] : req.query.products
  let bb = req.query.products.map(function(el) { return mongoose.Types.ObjectId(el) })
  query['$expr']['$and'].push({
    $and: [
      {
        items: {
          $in: ['item.itemId', bb]
        }
       }
     ]
   })
}

5 months ago ·

Santiago Trujillo

1 answers

Answer question

0

Answer question

Find remote jobs

Loading

Accepted Answer · 2022-03-25T17:01:24.903Z

Please see if the following addresses your requirement:

Mongoplayground link

Assumptions:

req.user.appId is a string.
bb is an array of either objectIds or strings ([ObjectId("asdfsd"),ObjectId("badsfds")], etc.)

const query = {
  "$expr": {
      $and: [{
        "$eq": ["$appId",req.user.appId],
      }]
      
  }
}

query['$expr']['$and'].push({
  $or: [
    {
      $in: [
        "$targetFrom",
        bb
      ]
    },
    {
      $in: [
        "$targetTo",
        bb
      ]
    }
  ]
})

Edit: Requirement is updated: Fetch all objs with a particular `appId`, where either `targetFrom` or `targetTo` is one of a given array, and `date` is between given `startDate` and `endDate`:

Monogplayground

In the above mongoplayground, I'm fetching all the objects where appId is "1", startDate is 2022-01-01, endDate is 2022-01-31 and bb is ["a","c","x"]. This should fetch all 3 docs, but the date filters out the third doc, because the date in that doc is 2021-12-24. If you change the date before that, you'll get all 3.

const query = {
  "$expr": {
      $and: [{
        "$eq": ["$appId",req.user.appId],
      }]

  }
}

query['$expr']['$and'] = query['$expr']['$and'].concat([{
          $and: [
            {
              $gte: [
                "$date",
                startDate
              ]
            },
            {
              $lt: [
                "$date",
                endDate
              ]
            }
          ]
        },{
  $or: [
    {
      $in: [
        "$targetFrom",
        bb
      ]
    },
    {
      $in: [
        "$targetTo",
        bb
      ]
    }
  ]
}])

Edit: Requirement updated: need to add `items.id` filter.

Mongoplayground Link

IF you want to filter documents out if no object in items has the given id, just add the extra object I added in the $and array in the $match stage.

{
 $in: [
       1,
       "$items.id",
 ]
}

IF you don't want to filter out such documents, but want to filter out all objects in items where the items.id is NOT the given ID, the only solution I could find is using a $project stage, added in the new mongoplayground link.

0

184