CODEWITHSUNDEEP
mongodbmongodb-queryaggregate
shevin varughese
shevin varughese

Reputation: 23

Match with OR clause after Unwind in MongoDB aggregate query

I have a MongoDB aggregate query like this

[{ "$match" : { "isDeleted" : false } }
, { "$match" : { "$or" : [{ "arrayCol.col_1" : "Yes", "arrayCol.subArrCol.col_2" : 10 }, { "arrayCol.subArrCol.col_3" : 2000 }] } }
, { "$project" : { "_id" : 0, "col_0" : "$col_0", "col_1" : "$arrayCol.col_1", "col_2" : "$arrayCol.subArrCol.col_2", "col_3" : "$arrayCol.subArrCol.col_3" } }]

which is currently returning 2 documents like below

/* 1 */
{
    "col_0" : "xyz",
    "col_1" : "Yes",
    "col_2" : [
        10
    ],
    "col_3" : [
        2013,
        1995
    ]
},

/* 2 */
{
    "col_0" : "pqr",
    "col_1" : "Yes",
    "col_2" : [
        9,
        10
    ],
    "col_3" : [
        2000,
        2000
    ]
}

Now I want to unwind both "col_2" and "col_3", but it should return only those docs like before, ie those docs with either col_2 = 10 or col_3 = 2000.

My expected result set is like follows,

/* 1 */
{
    "col_0" : "xyz",
    "col_1" : "Yes",
    "col_2" : 10,
    "col_3" : 2013
},

/* 2 */
{
    "col_0" : "pqr",
    "col_1" : "Yes",
    "col_2" : 9,
    "col_3" : 2000
}
/* 3 */
{
    "col_0" : "pqr",
    "col_1" : "Yes",
    "col_2" : 10,
    "col_3" : 2000
}

If the match condition was an AND clause, then I could have added the same match condition once again after the unwinds to eliminate the wrong values. But how can I solve this in OR clause/ Can anyone please help? I am new to MongoDB.

Documents in my collection are as follows,

{
    "_id": "1234-5f33-4703-be7f-3ea679951af3",
    "col_0": "xyz",
    "arrayCol": {
        "col_1": "Yes",
        "subArrCol": [
            {
                "col_2": 10,
                "col_3": 2013
            },
            {
                "col_3": 1995
            }
        ]
    }
},
{
    "_id": "5678-5f33-4703-be7f-3ea679951af3",
    "col_0": "pqr",
    "arrayCol": {
        "col_1": "Yes",
        "subArrCol": [
            {
                "col_2": 9,
                "col_3": 2000
            },
            {
                "col_2": 10,
                "col_3": 2000,
                "col_4": "abc"
            }
        ]
    }
}

Upvotes: 1

Views: 345

Answers (1)

turivishal
turivishal

Reputation: 36104

You can try,

  • $unwind deconstruct col_3 and col_2 arrays
  • $match your conditions
  • $group by root to remove duplicate documents
  • $replaceWith to replace _id object to root document
  { $match: { ... } }, //skipped 
  { $project: { ... } }, //skipped 

  { $unwind: "$col_3" },
  { $unwind: "$col_2" },
  {
    $match: {
      $or: [
        {
          col_1: "Yes",
          col_2: 10
        },
        { col_3: 2000 }
      ]
    }
  },
  { $group: { _id: "$$ROOT" } },
  { $replaceWith: "$_id" }

Playground

Upvotes: 1

Related Questions