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

CodePudding user response：

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

CodePudding user response：

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