MongoDb get record based on dynamic keys

Question

I have a dynamic schema. In below schema "ABC", "WQA" are dynamic keys. I want to get records of those customers whose source is "sourceX" and want to show only "CustomerId" and "code".

I am a beginner in MongoDB domain. I tried different solutions like How to query a dynamic key - mongodb schema design but couldn't achieve desire output. Kindly guide me here.

{
 "_id" : ObjectId("5f0339e28fbb15b9f8a17181"), 
 "CustomerID" : "12345", 
 "ABC":[
   {
        "datetime" : ISODate("2020-06-13"), 
        "source" : "SourceX", 
        "code" : "ABC", 
   },
   {
       "datetime" : ISODate("2020-08-18"), 
       "source" : "SourceB", 
       "code" : "ABC",  
   }
  ],
"WQA":[
    {
        "datetime" : ISODate("2020-02-16"), 
        "source" : "SourceX", 
        "code" : "WQA", 
   },
   {
       "datetime" : ISODate("2020-03-16"), 
       "source" : "SourceY", 
       "code" : "WQA",  
   }
 ]
}

score 1 · Accepted Answer · answered Jul 24 '20 at 14:34

The following aggregation pipeline would be able to achieve what you're looking for, note however that this isn't a very mongo-friendly data format. https://mongoplayground.net/p/ZRcbzEe1bkR

db.collection.aggregate([
  {
    $project: {
      CustomerID: 1,
      entries: {
        $objectToArray: "$$ROOT"
      }
    }
  },
  {
    $unwind: "$entries"
  },
  {
    $unwind: "$entries.v"
  },
  {
    $match: {
      "entries.v.source": "SourceX"
    }
  },
  {
    $project: {
      _id: 0,
      CustomerID: 1,
      code: "$entries.v.code"
    }
  }
])

If you are able to, I strongly suggest you change your data to just have a single array, in which case you can easily utilize indexes to speed up queries. The following setup could utilize an index on codes.source to heavily speed up the initial match. https://mongoplayground.net/p/hBC1F-g9af1

{
  "_id": ObjectId("5f0339e28fbb15b9f8a17181"),
  "CustomerID": "12345",
  "codes": [
    {
      "datetime": ISODate("2020-06-13"),
      "source": "SourceX",
      "code": "ABC"
    },
    {
      "datetime": ISODate("2020-08-18"),
      "source": "SourceB",
      "code": "ABC"
    },
    {
      "datetime": ISODate("2020-02-16"),
      "source": "SourceX",
      "code": "WQA"
    },
    {
      "datetime": ISODate("2020-03-16"),
      "source": "SourceY",
      "code": "WQA"
    }
  ]
}

And then you could use a slightly modified aggregation pipeline to achieve the result you want.

db.collection.aggregate([
  {
    $match: {
      "codes.source": "SourceX"
    }
  },
  {
    $unwind: "$codes"
  },
  {
    $match: {
      "codes.source": "SourceX"
    }
  },
  {
    $project: {
      _id: 0,
      CustomerID: 1,
      code: "$codes.code"
    }
  }
])

MongoDb get record based on dynamic keys

1 Answers1