How to get multiple documents from one document in mongoDB?-CodePudding

I have multiple documents that needs to be queried from mongodb. Requirement is such that I need the nested array of documents has to be in a separate document under certain conditions. I have given a document sample for instance. In that I have an array of objects called cars, the output have to be such that the status is still inprogress and the key: parent_company is true then the objects that has parent_company_id equal to the _id of the parent_company: true and at least one of their model array has "v12". This might be confusing when explaining but please check on the document in db and the expected result, you'd get an idea on the requirement here.

If you take a look at the second JSON below, you can see those are the same Documents with different array of cars with certain conditions and that is how am expecting the results to be from the actual data from a collection So the condition is status has to be "inprogress" for "parent_company" : true and if any object under cars array has v12 in model then I need to get those documents that has the same parent_company_id.

Documents in db:

    {
    "_id" : ObjectId("63a8808652f40e1d48a3d1d7"),
    "name" : "A",
    "description" : null,
    "cars" : [
        {
            "id" : "63a8808c52f40e1d48a3d1da",
            "owner" : "John Doe",
            "purchase_date" : "2022-12-25,
            "status" : "inprogress",
            "parent_company" : true,
            "parent_company_id" : "63a8808c52f40e1d48a3d1da",
            "model" : [
              
            ]
        },
        {
            "id" : "63a880a552f40e1d48a3d1dc",
            "owner" : "John Doe 1",
            "purchase_date" : "2022-12-25,
            "parent_company" : false,
            "parent_company_id" : "63a8808c52f40e1d48a3d1da",
            "model" : [
               "v12"
            ]
        },
        {
            "id" : "63a880f752f40e1d48assddd"
            "owner" : "John Doe 1",
            "purchase_date" : "2022-12-25,
            "parent_company" : false,
            "parent_company_id" : "63a8808c52f40e1d48a3d1da",
            "model" : [
              
            ]
        },
        {
            "id" : "63a880f752f40e1d48a3d207"
            "owner" : "John Doe 11",
            "dt" : "2022-12-25,
            "status" : "inprogress",
            "parent_company" : true,
            "parent_company_id" : "63a880f752f40e1d48a3d207",
            "model" : [
               "v12"
            ]
        },
        {
            "id" : "63a880f752f40e1d48agfddd"
            "owner" : "John Doe 112",
            "dt" : "2022-12-25,
            "status" : "inprogress",
            "parent_company" : true,
            "parent_company_id" : "63a880f752f40e1d48agfddd",
            "model" : [
              
            ]
        }
    ]
}

Result I need

[
  {
    "_id" : ObjectId("63a8808652f40e1d48a3d1d7"),
    "name" : "A",
    "description" : null,
    "cars" : [
        {
            "id" : "63a8808c52f40e1d48a3d1da",
            "owner" : "John Doe",
            "purchase_date" : "2022-12-25,
            "status" : "inprogress",
            "parent_company" : true,
            "parent_company_id" : "63a8808c52f40e1d48a3d1da",
            "model" : [
              
            ]
        },
        {
            "id" : "63a880a552f40e1d48a3d1dc",
            "owner" : "John Doe 1",
            "purchase_date" : "2022-12-25,
            "parent_company" : false,
            "parent_company_id" : "63a8808c52f40e1d48a3d1da",
            "model" : [
               "v12"
            ]
        },
        {
            "id" : "63a880f752f40e1d48assddd"
            "owner" : "John Doe 1",
            "purchase_date" : "2022-12-25,
            "parent_company" : false,
            "parent_company_id" : "63a8808c52f40e1d48a3d1da",
            "model" : [
              
            ]
        }
    ]
 },
 {
    "_id" : ObjectId("63a8808652f40e1d48a3d1d7"),
    "name" : "A",
    "description" : null,
    "cars" : [
        {
           "id" : "63a880f752f40e1d48a3d207"
           "owner" : "John Doe 11",
           "dt" : "2022-12-25,
           "status" : "inprogress",
           "parent_company" : true,
           "parent_company_id" : "63a880f752f40e1d48a3d207",
           "model" : [
              "v12"
             ]
            }
    ]
 }
]

CodePudding user response：

There are lots of ways to do this, if I understand correctly. Here's one way.

db.collection.aggregate([
  {"$unwind": "$cars"},
  {
    "$group": {
      "_id": {
        "$and": [
          {"$eq": ["$cars.status", "inprogress"]},
          {"$eq": ["$cars.parent_company", true]},
          {"$eq": ["$cars.id", "$cars.parent_company_id"]},
          {"$in": ["v12", "$cars.model"]}
        ]
      },
      "root": {"$first": "$$ROOT"},
      "cars": {"$push": "$cars"}
    }
  },
  {
    "$replaceWith": {
      "$mergeObjects": [
        "$root",
        {"cars": "$cars"}
      ]
    }
  }
])

Try it on mongoplayground.net.

CodePudding user response：

Suggested Query I tried to execute but couldn't get an expected result. https://mongoplayground.net/p/O2DCwznv8o6