How to get MONGO docs based on few combinations having more that once

user_15581935 0
Viewed 21

How to get mongo docs based on few combinations having more that once in the below docs i need combination of "Location","Company","start","end" for only "Id" : "Q1D3" having this combination more than once but INT_ID will be different

{
    "Id" : "Q1D3",
    "INT_ID" : "1234",
    "Location" : "ENG",
    "Company" : "COMP1",
    "start" : 20191001,
    "end" : 20230930
}

{
    "Id" : "Q1D3",
    "INT_ID" : "1235",
    "Location" : "ENG",
    "Company" : "COMP1",
    "start" : 20191001,
    "end" : 20230930
}

{
    "Id" : "Q1D3",
    "INT_ID" : "1236",
    "Location" : "USA",
    "Company" : "COMP3",
    "start" : 20191001,
    "end" : 20230930
}

{
    "Id" : "Q1D3",
    "INT_ID" : "1237",
    "Location" : "USA",
    "Company" : "COMP3",
    "start" : 20191001,
    "end" : 20230930
}

{
    "Id" : "Q1D5",
    "INT_ID" : "1237",
    "Location" : "ENG",
    "Company" : "COMP1",
    "start" : 20191001,
    "end" : 20230930
}

{
    "Id" : "Q1D5",
    "INT_ID" : "1239",
    "Location" : "ENG",
    "Company" : "COMP1",
    "start" : 20191001,
    "end" : 20230930
}


{
    "Id" : "Q1D3",
    "INT_ID" : "1237",
    "Location" : "CAN",
    "Company" : "COMP1",
    "start" : 20191001,
    "end" : 20230930
}

from the above example i need to query only to get docs

{
    "Id" : "Q1D3",
    "INT_ID" : "1234",
    "Location" : "ENG",
    "Company" : "COMP1",
    "start" : 20191001,
    "end" : 20230930
}

{
    "Id" : "Q1D3",
    "INT_ID" : "1235",
    "Location" : "ENG",
    "Company" : "COMP1",
    "start" : 20191001,
    "end" : 20230930
}

{
    "Id" : "Q1D3",
    "INT_ID" : "1236",
    "Location" : "USA",
    "Company" : "COMP3",
    "start" : 20191001,
    "end" : 20230930
}

{
    "Id" : "Q1D3",
    "INT_ID" : "1237",
    "Location" : "USA",
    "Company" : "COMP3",
    "start" : 20191001,
    "end" : 20230930
}

can some one please help me on this

mongodb-query mongodb
1 Answers
  • $match your condition
  • $group by Location, Company, start and end fields and make array of documents in doc
  • $unwind deconstruct doc array
  • $group by INT_ID and first doc
  • $replaceRoot to replace doc object to root
db.collection.aggregate([
  { $match: { Id: "Q1D3" } },
  {
    $group: {
      _id: {
        Location: "$Location",
        Company: "$Company",
        start: "$start",
        end: "$end"
      },
      doc: { $push: "$$ROOT" }
    }
  },
  { $unwind: "$doc" },
  {
    $group: {
      _id: "$doc.INT_ID",
      doc: { $first: "$doc" }
    }
  },
  { $replaceRoot: { newRoot: "$doc" } }
])

Playground

user_89871280
Related