find index of particular element in multi-dimensional array in MongoDB-CodePudding

I've got a document in MongoDB which has a multi-dimensional array as shown below.

{"_id":1,
     "name":"Johnson",
     "card":[
             ["",12,25,"","",52,60,"",86],
             [1,17,29,"",43,"","","",89],
             [3,"","",34,45,"",62,70,""]
           ]
    }

I'm looking for a query that returns the index of a particular element in the array, for example, say 29 whose index is [1][2] but when i queried as:

> db.test.aggregate([{$project:{index:{$indexOfArray:["$card",29]}}}])

i got the result as:

{ "_id" : 1, "index" : -1 }

which is not true. I found that this query method works only for one-dimensional array and I'm unable to figure out how to find the index of multi-dimensional array in MongoDB. Any help will be appreciated. Thankyou

CodePudding user response：

Not exactly clear on what datatype [1][2] is, so rendering the desired output is a bit of a challenge. Here is a attempt to help your question...

Test Data

db.collection.insert(
{
    "name":"Johnson",
    "card":[
        ["", 12, 25, "", "", 52, 60, "", 86],
        [1, 17, 29, "", 43, "", "", "", 89],
        [3, "", "", 34, 45, "", 62, 70, ""]
    ]
}

(Assumes a hard-coded value of 29 to search for)

Aggregate

EDIT 2021-12-09 - ADDED $project TO CAST RESULTS AS INTEGER. WAS NumberLong()

db.collection.aggregate([
    {
        $unwind:
        {
            path: "$card",
            includeArrayIndex: "outerIndex"
        }
    },
    {
        $unwind:
        {
            path: "$card",
            includeArrayIndex: "innerIndex"
        }
    },
    {
        $match:
        {
            "card": 29
        }
    },
    {
        $project:
        {
            name: 1,
            card: 1,
            outerIndex: { $convert: { input: "$outerIndex", to: "int" } },
            innerIndex: { $convert: { input: "$innerIndex", to: "int" } }
        }
    }
])

Results

[
  {
    _id: ObjectId("61b13476c6c466d7d1ea9b5e"),
    name: 'Johnson',
    card: 29,
    outerIndex: 1,
    innerIndex: 2
  }
]

Unwanted fields can be supressed with another $project stage, but I did not include it here since I was not clear on desired output.