Querying specific document within nested arrays

Working with Data
1

Hi

We have a collection Account that has multiple nested arrays.

Each account has a Customer, and each customer has Policy, and each policy has Document.

Account:
{
  email: string
  customers: [{
    id: '495',
    policies: [{
      ref: '123',
      documents: [{
        id: '123',
        type: '1'
      }, {
        id: '321',
        type: '2'
      }]
    }]
  }]
}

If we are trying to find a specific document within an account, we must query through all the customers, and their policies, and then find the document.

We are doing it as so:

function getPolicyDocument (documentId) {
  return collection.findOne({  'customers.policies.documents.id': documentId })
}

but it returns the whole account instead of just the document.

We tried using projection:

function getPolicyDocument (documentId) {
  return collection.findOne({  
    'customers.policies.documents.id': documentId 
   }, {  'customers.policies.documents.id': 1 })
}

but it also returns everything, except the document only has id field within it.

elemMatch also seems not to work since we are dealing with nested arrays.

Is there any way we can get a specific document within nested arrays?

Thanks

2

Hi @Vitor_Rodrigues and welcome in the MongoDB Community :muscle: !

I inserted this well formatted document in the account collection:

{
  "email": "foo@moo.com",
  "customers": [
    {
      "id": "495",
      "policies": [
        {
          "ref": "123",
          "documents": [
            {
              "id": "123",
              "type": "1"
            },
            {
              "id": "321",
              "type": "2"
            }
          ]
        }
      ]
    }
  ]
}

The find command always returns the documents from the collection. You can use project to select some top level fields and do a few tricks, but that’s about it. The final result always looks like the initial document, more or less.
See the doc: https://docs.mongodb.com/manual/reference/method/db.collection.find/#std-label-find-projection

If you want to transform completely your output, the easiest way is to use the aggregation pipeline. Here is how you can do what you want:

let pipeline = [
  {
    '$match': {
      'customers.policies.documents.id': '123'
    }
  }, {
    '$unwind': {
      'path': '$customers'
    }
  }, {
    '$unwind': {
      'path': '$customers.policies'
    }
  }, {
    '$unwind': {
      'path': '$customers.policies.documents'
    }
  }, {
    '$match': {
      'customers.policies.documents.id': '123'
    }
  }, {
    '$replaceRoot': {
      'newRoot': '$customers.policies.documents'
    }
  }
]

Result:

> db.account.aggregate(pipeline)
[ { id: '123', type: '1' } ]

I’m not particularly proud of this answer to be honest. There is probably a smarter way to achieve this using $slice or $elemMatch with a projection, but I can’t find a more elegant solution at the moment.

I hope you have an index on the customers.policies.documents.id field :wink: !

Cheers,
Maxime.