CODEWITHSUNDEEP
c#mongodblinqazure-cosmosdb
Tony
Tony

Reputation: 17637

Find n-level nested SubDocument MongoDB

I have this Document:

{
  "$id": "1",
  "DocType": "Unidade",
  "Nome": "TONY",
  "RG_InscricaoEstadual": "4347924938742",
  "Setores": [
    {
      "$id": "9",
      "Nome": "Child0",
      "Setores": [
        {
          "$id": "10",
          "Nome": "Child1",
          "Setores": [
                     /* <n depth nested level> */
                     "$id": "11",
                     "Nome": "Child2",
                     "Id": "90228c56-eff2-46d2-a324-b04e3c69e15c",
                     "DocType": "Setor"
          ],
          "Id": "60228c56-dff2-46d2-a324-b04e3c69e15b",
          "DocType": "Setor"
        }
      ],
      "Id": "8457e1b7-39dc-462c-8f46-871882faea2c",
      "DocType": "Setor"
    }
  ]
}

How to query this SubDocument if I want to retrieve a Setor, for example 

"Id": "60228c56-dff2-46d2-a324-b04e3c69e15b"

I know that if I know now many levels it is Nested, I can write a query to look for something like 

Unidade.Setor.Id=="8457e1b7-39dc-462c-8f46-871882faea2c"

But How can I search for it for a unknown number of nested Levels, for example 1, 2, 3 n levels?

How to find the Setor with Id '90228c56-eff2-46d2-a324-b04e3c69e15c', for example?

Comments about how to solve this question will also be appreciated.

Upvotes: 1

Views: 1139

Answers (2)

Jambor - MSFT
Jambor - MSFT

Reputation: 3293

In c# we can create a recursive method to achieve this scenario.

Here is a BsonDocument I created:

 BsonDocument doc = new BsonDocument {
            { "id","1"},
            { "DocType", "Unidade"},
            { "Nome", "TONY"},
            { "RG_InscricaoEstadual", "4347924938742"},
            { "Setores",new BsonArray {
             new BsonDocument {
             { "id","9" },
             { "Nome", "Child0"},
             { "Setores", new BsonArray { new BsonDocument {
                          { "id","10" },
                          { "Nome", "Child1"},
                          { "Setores", new BsonArray { new BsonDocument {
                                       { "id","11" },
                                       { "Nome", "Child2"},
                                       {  "Id","90228c56-eff2-46d2-a324-b04e3c69e15c" },
                                       { "DocType", "Setor"}
                                       }
                                       }
                          },
                          {  "Id","60228c56-dff2-46d2-a324-b04e3c69e15b" },
                          { "DocType", "Setor"}
                 }
                 }
                 },
                 {  "Id","8457e1b7-39dc-462c-8f46-871882faea2c" },
                 { "DocType", "Setor"}
                 }
            }
            }
            };

You can use Mongo c# Query method to get this BsonDocument from MongoDB.

Here is a recursive method I used to query document via "Id": BsonDocument result = GetID(doc, "90228c56-eff2-46d2-a324-b04e3c69e15c");

 public static BsonDocument GetID(BsonDocument doc, string queryId)
    {
        BsonDocument result = new BsonDocument();
        if (doc.Elements.Where(c => c.Name == "Setores").Count() != 0)
        {
            foreach (var item in doc.GetElement("Setores").Value.AsBsonArray)
            {
                var id = item.AsBsonDocument.GetElement("Id").Value;
                if (id == queryId)
                {
                    result = item.AsBsonDocument;
                    break;
                }
                result = GetID(item.AsBsonDocument, queryId);
            }
        }
        return result;
    }

I hope this could give you some tips.

Upvotes: 3

Dan Green-Leipciger
Dan Green-Leipciger

Reputation: 3922

The only thing you could do is have nested queries i.e.

find({"Unidade.Setor.Id": ObjectId("8457e1b7-39dc-462c-8f46-871882faea2c")
find({"Unidade.Setor.Setor.Id": ObjectId("8457e1b7-39dc-462c-8f46-871882faea2c")
find({"Unidade.Setor.Setor.Setor.Id": ObjectId("8457e1b7-39dc-462c-8f46-871882faea2c")

Run then one after the other if the previous one fails.

But DON'T!!!

You should be storing these Setor records as separate documents. You can store them with references to each other and then query for them using lookup (like a join in SQL)

https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/

Upvotes: 1

Related Questions