Conditional match on an existence of a field in collection

user_4261973 0
Viewed 109

Please help me. I have a collection as below

[
   {
      "_id":{
         "$oid":"62a3673660e2f16c7a7bc088"
      },
      "merchant":{
         "$oid":"62a3640560e2f16c7a7bc078"
      },
      "title":"24 Test 1",
      "filter_conditions":{
         "city":[
            "AAA",
            "BBB",
            "CCC",
            "DDD"
         ],
         "state":[
            
         ],
         "pincode":[
            "12345"
         ]
      }
   },
   {
      "_id":{
         "$oid":"62a3673660e2f16c7a7bc089"
      },
      "merchant":{
         "$oid":"62a3640560e2f16c7a7bc079"
      },
      "title":"24 Test 2",
      "filter_conditions":{
         "city":[
            "AAA",
            "BBB"
         ]
      }
   }
]

I want to filter data based on pincode/city/state

if pincode is present match it and ignore city and state elseif city is present match it and ignore state else match on state

aggregation-framework mongodb-query mongodb
2 Answers

You can use an aggregation pipeline with a $filter:

  1. If any of the fields does not exist on the doc, create it with an empty array.
  2. Use $filter to grade the docs, so the grade for matching pincode is 100, for matching city is 10 for matching state is 1. Use $max to keep the best grade only.
  3. Return the doc with highest grade.
db.collection.aggregate([
  {$set: {
      "filter_conditions.pincode": {$ifNull: ["$filter_conditions.pincode", []]},
      "filter_conditions.city": {$ifNull: ["$filter_conditions.city", []]},
      "filter_conditions.state": {$ifNull: ["$filter_conditions.state", []]}
    }
  },
  {$set: {
      grade: {
        $max: [
          {$multiply: [
              {$size: {
                  $filter: {
                    input: "$filter_conditions.pincode",
                    as: "item",
                    cond: {$eq: ["$$item", "12345"]}
                  }
                }
              }, 100]
          },
          {$multiply: [
              {$size: {
                  $filter: {
                    input: "$filter_conditions.city",
                    as: "item",
                    cond: {$eq: ["$$item", "BBB"]}
                  }
                }
              }, 10]
          },
          {$multiply: [
              {$size: {
                  $filter: {
                    input: "$filter_conditions.state",
                    as: "item",
                    cond: {$eq: ["$$item", "AL"]}
                  }
                }
              }, 1]
          }
        ]
      }
    }
  },
  {$sort: {grade: -1}},
  {$limit: 1}
])

See how it works on the playground example

user_184823100

You can work with nested $cond to perform the filtering.

Concept:

  1. Check filter_conditions.pincode is existed.

    1.1. If true, check the value is existed in filter_conditions.pincode array.

    1.2. Else, proceed to 2.

  2. Check filter_conditions.city is existed.

    2.1. If true, check the value is existed in filter_conditions.city array.

    2.2. Else, proceed to 3.

  3. Check if value is existed in filter_conditions.state array (default as empty array if the array is not existed).

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $cond: {
          if: {
            $ne: [
              "$filter_conditions.pincode",
              undefined
            ]
          },
          then: {
            $in: [
              "", // pincode value
              "$filter_conditions.pincode"
            ]
          },
          else: {
            $cond: {
              if: {
                $ne: [
                  "$filter_conditions.city",
                  undefined
                ]
              },
              then: {
                $in: [
                  "", // city value
                  "$filter_conditions.city"
                ]
              },
              else: {
                $in: [
                  "", // state value
                  {
                    $ifNull: [
                      "$filter_conditions.state",
                      []
                    ]
                  }
                ]
              }
            }
          }
        }
      }
    }
  }
])

Sample Mongo Playground

user_80176900
Related