CODEWITHSUNDEEP
mongodbnosqlaggregation-framework
Bruno Cerk
Bruno Cerk

Reputation: 355

$filter upto 2 nested level in mongodb

i have a database structured like this:

{
  "Hospitais": [
    {
      "utis": [
        {
          "_id": 893910,
          "nome": "UTI1",
          "leitos": [
            {
              "_id": 128938120,
              "_paciente": "Oliver"
            },
            {
              "_id": 12803918239,
              "_paciente": "Priscilla"
            }
          ]
        },
        {
          "_id": 38471839,
          "nome": "UTI2",
          "leitos": [
            {
              "_id": 48102938109,
              "_paciente": "Serveró"
            },
            {
              "_id": 501293890,
              "_paciente": "Thales"
            }
          ]
        },
        {
          "_id": 58109238190,
          "nome": "UTI3",
          "leitos": [
            {
              "_id": 93801293890,
              "_paciente": "Lucia"
            },
            {
              "_id": 571029390,
              "_paciente": "Amanda"
            }
          ]
        }
      ],
      "nome": "Dorio Silva"
    },
    {
      "utis": [
        {
          "_id": 410923810,
          "nome": "UTI1",
          "leitos": [
            {
              "_id": 48102938190,
              "_paciente": "Neymar"
            },
            {
              "_id": 48102938190,
              "_paciente": "Thiago"
            }
          ]
        },
        {
          "_id": 41092381029,
          "nome": "UTI2",
          "leitos": [
            {
              "_id": 10293182309,
              "_paciente": "Brazza"
            },
            {
              "_id": 38190238,
              "_paciente": "Pelé"
            }
          ]
        },
        {
          "_id": 83102938109,
          "nome": "UTI3",
          "leitos": [
            {
              "_id": 810923810,
              "_paciente": "Aparecida"
            },
            {
              "_id": 20938904209,
              "_paciente": "Pimentinha"
            }
          ]
        }
      ],
      "nome": "Apart Hospital"
    }
]
}

I need to return just the array of Hospitais that have a match with a input of 'Hospitais.nome' and also, there is a array inside each array of Hospitais called 'utis', i want to filter it too together, there is a "Expected Result" bellow.

I have tried this from many different ways and the most recent one i've been trying is this code :

db.collection.aggregate(
[
    { "$match": { "Hospitais.nome": 'Dorio Silva'} },
    {
        "$project": {
            _id: 0,
            Hospitais: {
                $filter: {
                    input: "$Hospitais",
                    as: "hospital",
                    cond: { $and: [{$eq: ["$$hospital.nome", 'Dorio Silva']},{ $eq: ["$$hospital.utis.nome",'UTI1']}]}
                }
            }
        }
    }
]

);

As far as i understood about aggregation filter, it should only show the objects of the array Hospitais where Hospital.nome is 'Dorio Silva' and the object of Hospital.utis where Hospital.utis.nome is 'UTI1'

What i was expecting:

[
	{
		"utis" : [
			{
				"_id" : NumberInt("893910"),
				"nome" : "UTI1",
				"leitos" : [
					{
						"_id" : NumberInt("128938120"),
						"_paciente" : "Oliver"
					},
					{
						"_id" : NumberLong("12803918239"),
						"_paciente" : "Priscilla"
					}
				]
			}
		],
		"nome" : "Dorio Silva"
	}
]

But thats never the result, i can post the results if asked, but i don't think it may be needed. What is the right way to query my result? Is there anything wrong with the way i'm building my database? It could be done better?

Upvotes: 1

Views: 711

Answers (1)

Ashh
Ashh

Reputation: 46481

You need to $unwind the first array then you can easily apply $filter on the nested array

db.collection.aggregate([
  { "$unwind": "$Hospitais" },
  { "$match": { "Hospitais.nome": "Dorio Silva" } },
  { "$project": {
    "Hospitais": {
      "$filter": {
        "input": "$Hospitais.utis",
        "as": "uti",
        "cond": {
          "$eq": ["$$uti.nome", "UTI1"]
        }
      }
    }
  }}
])

Or you can try this as well

db.collection.aggregate([
  { "$match": { "Hospitais.nome": "Dorio Silva" } },
  { "$project": {
    "Hospitais": {
      "$filter": {
        "input": {
          "$map": {
            "input": "$Hospitais",
            "as": "hospital",
            "in": {
              "nome": "$$hospital.nome",
              "utis": {
                "$filter": {
                  "input": "$$hospital.utis",
                  "as": "uti",
                  "cond": {
                    "$eq": ["$$uti.nome", "UTI1"]
                  }
                }
              }
            }
          }
        },
        "as": "hospital",
        "cond": {
          "$eq": ["$$hospital.nome", "Dorio Silva"]
        }
      }
    }
  }}
])

Both will give the similar output

[
  {
    "Hospitais": [
      {
        "_id": 893910,
        "leitos": [
          {
            "_id": 1.2893812e+08,
            "_paciente": "Oliver"
          },
          {
            "_id": 1.2803918239e+10,
            "_paciente": "Priscilla"
          }
        ],
        "nome": "UTI1"
      }
    ]
  }
]

Upvotes: 1

Related Questions