Query MongoDB with array of sub-documents

Hi!

I have a MongoDB collection (actually in Azure CosmosDB) with documents that have an array with nested sub-documents:

    {
       "_id":{
          "$oid":"5e78XXXa3f4d"
       },
       "ObjectId":"0a1b",
       "UserName":"john@example.com",
       "Active":true,
       "BusinessUnits":[
          {
             "Number":"1234",
             "Name":"Johns Workshop"
          }
       ]
    }

Can someone post a specific example query how I would be able to retrieve the entire document (incl. all fields) in ReDash? I read that “$project” might be the right option here, but couldn’t get a query to work that would return the BusinessUnits.0.Number and Name fields.

How would you write this query in a Node or Python environment? From there, we’ll see if Redash’s query syntax can do the same. Redash uses pymongo under the hood so it would surprise me if this didn’t work.