Query Mongo document array based on another array in the same document

user_518973 0
Viewed 98

I have Mongo documents in the following format :

{
    "_id":{
        "$oid":"1237563648fe93j3b5"
    },
    "stage_id":{
        "$oid":"1213241423nn552l54j"
    },
    "payload":[
        {
            "_data":{
                "name":"name1",
                "description":"",
                "parent":"",
                "status":"NOT READY"
            }
        },
        {
            "_data":{
                "name":"name2",
                "description":"MS Visio",
                "parent":"A7704",
                "status":"OPERATING"
            }
        },

        {
            "_data":{
                "assetnum":"name3",
                "description":"MS Visio",
                "parent":"7127",
                "status":"OPERATING"
            }
        }
    ],
    "response":[
        {
            "_data":{
                "Error":{
                    "message":"AVJPA - The record already exists. 
                    "attrname":"name",
                    "reasonCode":"AVJPA",
                    "status":"400"
                }
            },
            "_meta":{
                "status":"400"
            }
        },
        {
            "_data":{
                "Error":{
                    "message":"AVJPA - The record already exists. 
                    "attrname":"name",
                    "reasonCode":"AVJPA",
                    "status":"400"
                }
            },
            "_meta":{
                "status":"400"
            }
        },
        {
            "_data":{
                "message":"success"
            },
            "_meta":{
                "status":"204"
            }
        }
    ]
}

There are a number of documents in this format. The response array is the responses for the items in the payload array, which were posted to an API endpoint. So the index 0 in the response array is the response for the index 0 payload in the payload array. Now I would like to filter the collection and would like to get the payloads where the response reasonCode IS AVJPA. How this can be done ? I am using the following code to find out the responses with Error exists, but not sure how to find the payloads that corresponds to the response. 

logs.find({
    "stage_id":1213241423nn552l54j
    $elemMatch: {"_responsedata.Error": {$exists: true}}
})

where logs is the name of my collection. I am running this code in Python and using PyMongo client library.

pymongo mongodb-query mongodb nosql arrays
1 Answers

Check if this meets your requirements:

db.logs.aggregate([
  {
    $match: {
      "stage_id": ObjectId("5a934e000102030405000001"),
      "response": {
        $elemMatch: {
          "_data.Error": {
            $exists: true
          }
        }
      }
    }
  },
  {
    $addFields: {
      tmp: "$payload"
    }
  },
  {
    $unwind: {
      path: "$tmp",
      includeArrayIndex: "idx",
      preserveNullAndEmptyArrays: false
    }
  },
  {
    $match: {
      $expr: {
        $eq: [
          {
            $let: {
              vars: {
                input: {
                  $arrayElemAt: [
                    "$response",
                    "$idx"
                  ]
                }
              },
              in: "$$input._data.Error.reasonCode"
            }
          },
          "AVJPA"
        ]
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      stage_id: {
        $first: "$stage_id"
      },
      payload: {
        $push: {
          $arrayElemAt: [
            "$payload",
            "$idx"
          ]
        }
      },
      response: {
        $push: {
          $arrayElemAt: [
            "$response",
            "$idx"
          ]
        }
      },

    }
  }
])

MongoPlayground

user_37104900
Related