Query two level data with $or operation

MongoDB Atlas
1

Assume that I have data below. I have a list of warehouse in the parent warehouse.

[
  {
    "item": "journal",
    "warehouseParent": "A",
    "instock": [
      {
        "warehouse": "B",
        "qty": 5
      },
      {
        "warehouse": "C",
        "qty": 15
      }
    ]
  },
  {
    "item": "paper",
    "warehouseParent": "X",
    "instock": [
      {
        "warehouse": "A",
        "qty": 60
      },
      {
        "warehouse": "G",
        "qty": 15
      },
      {
        "warehouse": "G",
        "qty": 16
      }
    ]
  }
]

If I have the want to query warehouse = “A” , the response that I want is

[
  {
    "item": "journal",
    "warehouseParent": "A"
  },
  {
    "item": "paper",
    "warehouseParent": "X",
    "instock": [
      {
        "warehouse": "A",
        "qty": 60
      }
    ]
  }
]

How to query the data like tihs in one command? because we have pagination and sorting to show the data that why I want mongo db to provide the correct data in one command.

I test with the query below but it not work with or operation.
{$and:[{ “instock.warehouse”: “A”} ,{“warehouse”:“A”}]}
with Project { “instock.$”: 1, “warehouse”: 1 }

Any suggestion?

2

Hi, I think you want a $or, not a $and (and means both conditions must be true):

    $match: {  
      $or: [  
        { "warehouseParent": "A" },  
        { "instock.warehouse": "A" }  
      ]  
    }
3

and if you want to show only the one that matches, you can: Mongo playground