Question 117550 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – Conditional match on an existence of a field in collection

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search