Get only matched array object along with parent fields-CodePudding

I also checked the following question and tried various other things but couldn't get it working

Retrieve only the queried element in an object array in MongoDB collection

I have the following document sample

{
    _id: ObjectId("634b08f7eb5cb6af473e3ab2"),
    name: 'India',
    iso_code: 'IN',
    states: [
        {
            name: 'Karnataka',
            cities: [
                {
                    name: 'Hubli Tabibland',
                    pincode: 580020,
                    location: { type: 'point', coordinates: [Array] }
                },
                {
                    name: 'Hubli Vinobanagar',
                    pincode: 580020,
                    location: { type: 'point', coordinates: [Array] }
                },
                {
                    name: 'Hubli Bengeri',
                    pincode: 580023,
                    location: { type: 'point', coordinates: [Array] }
                },
                {
                    name: 'Kusugal',
                    pincode: 580023,
                    location: { type: 'point', coordinates: [Array] }
                }
            ]
        }
    ]
}

I need only the following

{
    _id: ObjectId("634b08f7eb5cb6af473e3ab2"),
    name: 'India',
    iso_code: 'IN',
    states: [
        {
            name: 'Karnataka',
            cities: [
                {
                    name: 'Kusugal',
                    pincode: 580023,
                    location: { type: 'point', coordinates: [Array] }
                }
            ]
        }
    ]
}

Following is the query that I have tried so far but it returns all the cities

db.countries.find(
    {
        'states.cities': {
            $elemMatch: {
                'name' : 'Kusugal'
            }
        }
    }, 
    {
        '_id': 1, 
        'name': 1, 
        'states.name': 1, 
        'states.cities.$' : 1
    }
);

CodePudding user response：

I was able to achieve it with the help of aggregation.

db.countries.aggregate([
    { $match: { "states.cities.name": /Kusugal/ } }, 
    { $unwind: "$states" }, 
    { $unwind: "$states.cities" }, 
    { $match: { "states.cities.name": /Kusugal/ } }
]);

1st line $match will query the records with cities with only Kusugal

2nd & 3rd line $unwind will create a separate specific collection of documents from the filtered records

3rd line $match will filter these records again based on the condition

In simple aggregation processes commands and sends to next command and returns as an single result.