How to fetch key by value which is nested in mongoDB?

Question

I have below structure in MongoDB. I want to fetch a key by specific value.

ex. I want the result to be A1 if the value in v = 'Item Information'

I have few constraints through
1. key names A1, E1 = will be dynamic
2. value inside them 'v' will be always there

What should be the query for this?

Answer 1

To deal with dynamic keys you can try as below:

db.collection.aggregate([
        {
            $unwind: "$sheet"    
        },
        {
            $project: {
                "sheetData": { $objectToArray: "$sheet" }
            }
        },
        {
            $match: {
                 "sheetData.v": { $elemMatch: { v: 'Item Information' } },
            }
        },
        {
            $project: {
                result: "$sheetData.k"
            }
        }
    ])

Above query will give result keyName (A1) which contain "v": "Item Information"

The result will be as below:

{
    "_id" : ObjectId("5d020542c366c914650be72e"),
    "result" : [
        "A1"
    ]
}

I would like to let you know that I am trying all the above queries at my end and then only I am posting it along with the resulted JSON. So there could be only one possible mistake that we both are working with different document structure so please match your structure with below dummy Doc.

{
    "_id" : ObjectId("5d020542c366c914650be72e"),
    "sheet" : [
        {
            "A1" : [
                {
                    "t" : "value"
                },
                {
                    "v" : "Item Information"
                },
                {
                    "r" : "value"
                },
                {
                    "h" : "value"
                },
                {
                    "w" : "value"
                }
            ]
        },
        {
            "E1" : [
                {
                    "t" : "value"
                },
                {
                    "v" : "value"
                },
                {
                    "r" : "value"
                },
                {
                    "h" : "value"
                },
                {
                    "w" : "value"
                }
            ]
        }
    ]
}

Answer 2

Try as below to search Fetch Doc which contains "v": "Item Information" in "sheet.A1".

db.collection.aggregate([
    {
        $unwind: "$sheet"
    },
    {
        $match: {
             "sheet.A1": { $elemMatch: { v: 'Item Information' } },
        }
    }
])

Let me know if you need something else I will update my query.

I am working with dummy doc as below:

{
    "_id" : ObjectId("5d020542c366c914650be72e"),
    "sheet" : [
        {
            "A1" : [
                {
                    "t" : "value"
                },
                {
                    "v" : "Item Information"
                },
                {
                    "r" : "value"
                },
                {
                    "h" : "value"
                },
                {
                    "w" : "value"
                }
            ]
        },
        {
            "E1" : [
                {
                    "t" : "value"
                },
                {
                    "v" : "value"
                },
                {
                    "r" : "value"
                },
                {
                    "h" : "value"
                },
                {
                    "w" : "value"
                }
            ]
        }
    ]
}

Result of the above query will be as below:

{
    "_id" : ObjectId("5d020542c366c914650be72e"),
    "sheet" : {
        "A1" : [
            {
                "t" : "value"
            },
            {
                "v" : "Item Information"
            },
            {
                "r" : "value"
            },
            {
                "h" : "value"
            },
            {
                "w" : "value"
            }
        ]
    }
}