I have following documents. Note that the top level document has ID and attribute1 in common with subDocument.

{
    "ID": "1",
    "attribute1": "123",
    "subDocument": [
      {
        "ID": "2",
        "attribute1": "456",
      },
      {
        "ID": "3",
        "attribute1": "567",
      }
    ]
  },
  {
    "ID": "4",
    "attribute1": "123",
    "subDocument": [
      {
        "ID": "5",
        "attribute1": "456",
      }
    ]
  }

Now I need to have a query that can search based on ID and attribute1. If I give ID =1,attribute1="123", it should return the first top level document like following:

{
    "ID": "1",
    "attribute1": "123"
  }

If I give ID =2,attribute1="456", it should return the item of subDocument like following:

     {
        "ID": "2",
        "attribute1": "456",
      }

How can I do that? playground

CodePudding user response：

You can create a union of root documents and sub documents by using $unionWith. Then apply your filter on the union result.

db.collection.aggregate([
  {
    $project: {
      "ID": 1,
      "attribute1": 1
    }
  },
  {
    "$unionWith": {
      "coll": "collection",
      "pipeline": [
        {
          "$unwind": "$subDocument"
        },
        {
          "$replaceRoot": {
            "newRoot": "$subDocument"
          }
        }
      ]
    }
  },
  {
    $match: {
      // your criteria here
      "ID": "2",
      "attribute1": "456",
      
    }
  }
])

Here is the Mongo playground for your reference.

CodePudding user response：

Query

• adds the id/attibute of the root as subdocument
• unwind and replace ROOT => each member become ROOT document
• $match to match the document with variables

Test code here

aggregate(
[{"$project": 
   {"subDocument": 
     {"$concatArrays": 
       ["$subDocument", [{"ID": "$ID", "attribute1": "$attribute1"}]]}}},
 {"$unwind": {"path": "$subDocument"}},
 {"$replaceRoot": {"newRoot": "$subDocument"}},
 {"$match" : {"ID" : your_id_var , "attribute1" : your_attr_var}}])

CodePudding user response：

Do we know Why the Schema is like this where you put ID and attribute1 at document level and also at sub document level? though we use this Schema the document returned you need is just an same filter values what you passed in Query? Is it possible that document ID and subdocument ID will clash?

Any ways still you want to get this result the use below:

If you want to persist Document & subdocument based on matching then use this : https://mongoplayground.net/p/e0PaEvZhXIf or https://mongoplayground.net/p/d8IKfSW4h87

However now you just need whatever is matched then use below https://mongoplayground.net/p/Ww1WqmY6wrV