MongoDB query using values priority

Question

I have following documents in my collection:

/* 1 */
{
    "_id" : ObjectId("5ea32d11f213c27c35395fd3"),
    "name" : "Test",
    "state" : "OH",
    "code" : "CDM"
}

/* 2 */
{
    "_id" : ObjectId("5ea32d29f213c27c35395fe0"),
    "name" : "Test1",
    "state" : "ALL",
    "code" : "CDM"
}

/* 3 */
{
    "_id" : ObjectId("5ea32d38f213c27c35395fe7"),
    "name" : "Test2",
    "state" : "OH",
    "code" : "ALL"
}

/* 4 */
{
    "_id" : ObjectId("5ea32d46f213c27c35395feb"),
    "name" : "Test3",
    "state" : "ALL",
    "code" : "ALL"
}

Trying to filter documents based on state and code.

But there are few criteria if particular state or code is not found then search with value ALL

Example 1:

state = CA

code = CDM

then return only

  {
    "_id" : ObjectId("5ea32d29f213c27c35395fe0"),
    "name" : "Test1",
    "state" : "ALL",
    "code" : "CDM"
}

Example 2:

state = CA

code = DCM

then return only

  {
    "_id" : ObjectId("5ea32d46f213c27c35395feb"),
    "name" : "Test3",
    "state" : "ALL",
    "code" : "ALL"
}

etc..

The query that i tried was state = CA ,code = CDM:

db.getCollection('user_details').aggregate([

{'$match': { 'state': {'$in': ['CA','ALL']},
'code': {'$in': ['CDM','ALL']}}}

])

return was:

/* 1 */
{
    "_id" : ObjectId("5ea32d29f213c27c35395fe0"),
    "name" : "Test1",
    "state" : "ALL",
    "code" : "CDM"
}

/* 2 */
{
    "_id" : ObjectId("5ea32d46f213c27c35395feb"),
    "name" : "Test3",
    "state" : "ALL",
    "code" : "ALL"
}

Expected was :

{
        "_id" : ObjectId("5ea32d29f213c27c35395fe0"),
        "name" : "Test1",
        "state" : "ALL",
        "code" : "CDM"
    }

Could you help to solve this issue.

Do you've to return { "_id" : ObjectId("5ea32d46f213c27c35395feb"), "name" : "Test3", "state" : "ALL", "code" : "ALL" } if both are not found ? — whoami - fakeFaceTrueSoul
– whoami - fakeFaceTrueSoul, Commented Apr 24, 2020 at 18:46
Also if you've both { "_id" : ObjectId("5ea32d29f213c27c35395fe0"), "name" : "Test1", "state" : "ALL", "code" : "CDM" } & { "_id" : ObjectId("5ea32d29f213c27c35395fe1"), "name" : "Test2", "state" : "CA", "code" : "CDM" } then what has to be returned - Only the 2nd Doc ? — whoami - fakeFaceTrueSoul
– whoami - fakeFaceTrueSoul, Commented Apr 24, 2020 at 18:56
no document should be returned ? Ok this is weird (Please edit the question for those scenarios) - with all these complications you can better opt to do it in code offloading stress on DB, Meanwhile you can try this :: (mongoplayground.net/p/ejx5TCZyNEr) — whoami - fakeFaceTrueSoul
– whoami - fakeFaceTrueSoul, Commented Apr 24, 2020 at 19:03

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-04-24 19:30:14Z

1

Try below query :

db.collection.find({
  $or: [
    {
      state: "CA",
      code: "CDM"
    },
    {
      state: "ALL",
      code: "CDM"
    },
    {
      state: "ALL",
      code: "ALL"
    },
    {
      state: "CA",
      code: "ALL"
    }
  ]
})

So basically you can not achieve what you're looking for in one query, you can try aggregation operator $facet & $switch to do this but I would not prefer to do that cause each stage in facet will do the given operation on entire collection's data. Instead get fewer docs using above query & in your code you can actually filter for best suited doc, Which would be easy & efficient. Don't forget to maintain indexes on DB.

Test : mongoplayground

answered Apr 24, 2020 at 19:30

whoami - fakeFaceTrueSoul

18.1k6 gold badges35 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Valijon · Accepted Answer · 2020-04-24 20:13:11Z

Try this one:

db.user_details.aggregate([
  {
    $facet: {
      match: [
        {
          "$match": {
            $or: [
              {
                "state": "CA",
                "code": {
                  "$in": [ "CDM", "ALL" ]
                }
              },
              {
                "state": {
                  "$in": [ "CA", "ALL"]
                },
                "code": "CDM"
              }
            ]
          }
        }
      ],
      all: [
        {
          "$match": {
            "state": "ALL",
            code: "ALL"
          }
        }
      ]
    }
  },
  {
    $project: {
      match: {
        $cond: [
          {
            $eq: [ { $size: "$match" },  0]
          },
          "$all",
          "$match"
        ]
      }
    }
  },
  {
    $unwind: "$match"
  },
  {
    $replaceWith: "$match"
  }
])

MongoPlayground

Collectives™ on Stack Overflow

MongoDB query using values priority

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related