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!

CodePudding user response：

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

CodePudding user response：

MongoDB is essentially a key, value pair database. It is not possible to write a query against an unknown key. You should refactor your schema to avoid putting value items in key fields.