I have two mongoDB queries one is aggregate and another one is a find. They are coded in a way that if "aggregate" query gives result then "find" query doesn't run, otherwise, if "aggregate" gives no result then find query runs. In the following way:-

var pipeline1 = [{
      $match: { "user_id": "123" } //dynamic value based on request
    }, {
      $lookup: {
        from: "config_rules",
        localField: "group_id",
        foreignField: "rule_type_value", //this field has group id mapped || or can be null
        as: "rule"
      }
    },
    {
      $unwind: "$rule"
    },{
      $match:{ "rule.configtype": "profile" } //dynamic value based on request
    }];
db.getCollection("user_group_mapping").aggregate(pipeline);

If the above aggregate gives a result then, the same is returned. or else we run the following find query to get config rule for the general user, and return it

var query = {
    $and: [
        { rule_type_value: null }, //null for general user rules
        { configtype: "profile" }
    ]
}
db.getCollection("config_rules").find(query)

In simple words for a request, we check if the requester is in a group if yes, then we return config rule based on this group, If the requester is not in any group then we return general config rule.

So my query is as seen above these are two different query running on different collection, and requires two separate mongo calls. Can I somehow combine these queries into 1 query?, Like- If for a given user he is in a group return group-specific config or return general config rule.

I want to combine these so that in my code I will need to make only one DB call(this db call itself has both query consolidated in one) instead of two.

Sample document in user_group_mapping collection

{  "user_id": "123",
   "group_id": "beta_users"
},
{  "user_id": "213",
   "group_id": "alpha_testers";
}

Sample data in config_rules :

{  "rule_type_value":"beta_users",
   "configType": "help",
   "configVersion": "1.1"
},
{  "rule_type_value":null,
   "configType": "help",
   "configVersion": "1.0"
},
{  "rule_type_value":"alpha_testers",
   "configType": "help",
   "configVersion": "1.3"
}

Sample Input:

Req 1 user_id: "123" configType: "help"

Req 2 user_id : "678" configType: "help"

Sample output: (I have only written rule content for simplicity)

Req 1 config v1.1 will be returned

{  "rule_type_value":"beta_users",
   "configType": "help",
   "configVersion": "1.1"
}

Req 2 v1.0 will be returned

{  "rule_type_value":null,
   "configType": "help",
   "configVersion": "1.0"
}

CodePudding user response：

MongoDB aggregation does not have flow control, and it will not execute subsequent stages if there are no documents output from a stage.

If you want to retrieve 1 of 2 possible values from the linked collection, change the $lookup stage so that all potential documents are selected, and filter the returned list afterward. Perhaps something similar to:

[
  {$match: { "user_id": "123" }}, 
  {$lookup: {
        from: "config_rules",
        let: {targetgroup: "$group_id"},
        pipeline: [{$match:{
           configtype: "profile",
           $or:[
             {$expr:{$eq:["$rule_type_value","$$targetgroup"]}},
             { rule_type_value: null,  }
           ]
        }}],
        as: "rule"
  }},
  {$set: {
     rule: {$cond: {
       if: {$in: ["$group_id", "$rule.rule_type_value"]},
       then: {$filter: {
                 input: "$rule",
                 cond: {$eq: ["$group_id", "$$this.rule_type_value"]}
       }},
       else: "$rule"
  }},

CodePudding user response：

try:

https://mongoplayground.net/p/m3HxBQIuqpS

• please set configType at line 23 and set user_id at line 31

db.config_rules.aggregate([
  {
    $lookup: {
      from: "user_group_mapping",
      localField: "rule_type_value",
      foreignField: "group_id",
      as: "rule"
    }
  },
  {
    $addFields: {
      "ruleCount": {
        $size: "$rule",
      },
      "user_id": {
        $first: "$rule.user_id"
      }
    }
  },
  {
    $match: {
      "configType": "help"
    }
  },
  {
    $match: {
      $or: [
        {
          user_id: {
            $eq: "678"//123 or 678
          }
        },
        {
          user_id: {
            $exists: false
          }
        }
      ]
    }
  },
  {
    $sort: {
      "ruleCount": -1
    }
  },
  {
    $limit: 1
  },
  {
    $project: {
      "_id": 0,
      "rule_type_value": 1,
      "configType": 1,
      "configVersion": 1
    }
  }
])