How to make a query on nested mongodb array by index?

Question

Document looks like this.

{
    'tid': 1,
    'matches': [{
        'dord': 1,
        'matches': [{
                'tord': 1,
                'score': 11
            },
            {
                'tord': 2,
                'score': 12
            }
        ]
    },
    {
        'dord': 2,
        'matches': [{
                'tord': 1,
                'score': 21
            },
            {
                'tord': 2,
                'score': 22
            }
        ]
    }]
}

You can see that it is a nested array. I want to extract the element at matches.1.matches.0 . Result should look like this:

{
  'tord': 2, 
  'score': 22
}

How do I do this ?

Takis · Accepted Answer · 2021-09-04 18:47:21Z

1

Bellow are 3 ways

The main problem is that we cant use .index in path.

Test code here

Query1 (move forward with temp variables)

get array index with "$arrayElemAt"
get document member with a temp variable

db.collection.aggregate([
  {
    "$set": {
      "member": {
        "$let": {
          "vars": {
            "m1": {
              "$arrayElemAt": [
                "$matches",
                1
              ]
            }
          },
          "in": {
            "$arrayElemAt": [
              "$$m1.matches",
              1
            ]
          }
        }
      }
    }
  },
  {
    "$project": {
      "member": 1
    }
  }
])

Test code here

Query2 (move forward with temp fields)

db.collection.aggregate([
  {
    "$set": {
      "member": {
        "$arrayElemAt": [
          "$matches",
          1
        ]
      }
    }
  },
  {
    "$set": {
      "member": {
        "$arrayElemAt": [
          "$member.matches",
          1
        ]
      }
    }
  },
  {
    "$project": {
      "member": 1
    }
  }
])

Test code here

Query3 (move forward to project the documents,move backwards for the arrays "$arrayElemAt")

db.collection.aggregate([
  {
    "$set": {
      "member": {
        "$arrayElemAt": [
          {
            "$arrayElemAt": [
              "$matches.matches",
              1
            ]
          },
          1
        ]
      }
    }
  },
  {
    "$project": {
      "member": 1
    }
  }
])

The more natural are the 1,2 i think because you only move forward, but there is option 3 also.

Which is the faster i dont know for sure, i think the 2.

edited Sep 4, 2021 at 18:47

answered Sep 4, 2021 at 18:11

Takis

8,7552 gold badges17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Saurav Pathak Over a year ago

Nice Answer. Thanks. What's the name of the painting in your profile picture ?

Takis Over a year ago

The painter name is Eşref Armağan, and he is blind. see this I dont know many information but its a nice happy painting.

Muhammad Mujtaba Shafique · Accepted Answer · 2021-09-04 18:55:01Z

0

Use the following method

exports.findScore =  async (tid,tord,dord)=>{
    return await tournament.findOne({tid:tid},{matches:{$elemMatch:{tord:tord},{dord:dord}}});
};

answered Sep 4, 2021 at 18:55

Muhammad Mujtaba Shafique

2952 silver badges13 bronze badges

1 Comment

Saurav Pathak Over a year ago

You're not using index of array instead searching with value directly.

Collectives™ on Stack Overflow

How to make a query on nested mongodb array by index?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related