Mongodb: How to query nested documents and top level documents

user_7846454 0
Viewed 292

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

aggregation-framework mongodb-query mongodb
3 Answers

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

PLayMongo

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

EDIT

If you have too many fields to type them by hand, you can use this, that puts as sub-document all the fields except the "_id" and "subDocument"

PlayMongo

aggregate(
[{"$project": 
   {"subDocument": 
     {"$concatArrays": 
       ["$subDocument",
         [{"$arrayToObject": 
            {"$filter": 
              {"input": {"$objectToArray": "$$ROOT"},
               "cond": 
               {"$and": 
                 [{"$ne": ["$$this.k", "subDocument"]},
                  {"$ne": ["$$this.k", "_id"]}]}}}}]]}}},
  {"$unwind": {"path": "$subDocument"}},
  {"$replaceRoot": {"newRoot": "$subDocument"}}
  {"$match" : {"ID" : your_id_var , "attribute1" : your_attr_var}}])

We dont have an operator like remove(object,k) so this makes object to array, fitlers and back to object (and its complicated).

There is another solution also, but its also complicated.

user_48826920

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.

user_147326690

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

user_64625380
Related