How to query a json array with unknown key in MongoDB?

Question

There is a MongoDB database with documents all like this:

{
    "_id": {
        "$oid": "62371a3c8200184f3b49d9e7"
    },
    "po_id": "123456",
    "po_detail": [{
        "10065": {
            "price": "123.45",
            "product": "P1"
        }
    }, {
        "11121": {
            "price": "678.90",
            "product": "P2"
        }
    }]
}

I want to find documents by "product"(such as "P1" or "P2"), but keys in array(such as "10065" or "11121") are unknown.

How to query documents like this in MongoDB(by using pymongo)?

Thank you!

Answer 1

find documents by "product" (such as "P1" or "P2") is not so clear. In general the data model is rather poor and you should consider a re-design.

The query could look like this:

db.collection.aggregate([
  { $unwind: "$po_detail" },
  {
    $set: {
      po_detail: { $first: { $objectToArray: "$po_detail" } }
    }
  },
  { $match: { "po_detail.v.product": "P1" } },
  { $set: { po_detail: [ "$po_detail" ] } },
  { $set: { po_detail: { $arrayToObject: "$po_detail" } } }
])

Mongo Playground