We have following structure in our Mongo collection

{
    "_id" : ObjectId("5f98aeadbaf1ea001affe4c0"),
    "name" : "Temp",
    "campaigntype" : 3,
    "startdate" : ISODate("2021-01-01T00:00:00.000Z"),
    "enddate" : ISODate("2021-01-01T00:00:00.000Z"),
    "affiliatetag" : "",
    "promotedtitles" : [ 
        {
            "primaryisbn" : "9781453238431",
            "promoprice" : "1.99",
            "countries" : [ 
                "US", 
                "CA", 
                "AU", 
                "GB"
            ],
            "retailers" : [ 
                "ALL"
            ],
            "dlp" : "17.99",
            "notes1" : "History",
            "notes2" : "",
            "sequence" : 1
        }, 
        {
            "primaryisbn" : "9781504063562",
            "promoprice" : "1.99",
            "countries" : [ 
                "US", 
                "CA"
            ],
            "retailers" : [ 
                "ALL"
            ],
            "dlp" : "11.99",
            "notes1" : "Thrillers",
            "notes2" : "",
            "sequence" : 2
        }, 
        {
            "primaryisbn" : "9781497673984",
            "promoprice" : "0.99",
            "countries" : [ 
                "US", 
                "CA"
            ],
            "retailers" : [ 
                "ALL"
            ],
            "dlp" : "6.99",
            "notes1" : "Romantic Suspense",
            "notes2" : "",
            "sequence" : 3
        }, 
        {
            "primaryisbn" : "9780547526959",
            "promoprice" : "1.99",
            "countries" : [ 
                "CA"
            ],
            "retailers" : [ 
                "All"
            ],
            "dlp" : "17.99",
            "notes1" : "History",
            "notes2" : "",
            "sequence" : 4
        }, 
        {
            "primaryisbn" : "9781453274248",
            "promoprice" : "1.99",
            "countries" : [ 
                "US"
            ],
            "retailers" : [ 
                "All"
            ],
            "dlp" : "9.99",
            "notes1" : "Historical Fiction",
            "notes2" : "",
            "sequence" : 5
        }
    ],
    "active" : true,
    "createdby" : ObjectId("5d2e2755d3851f0012108a05")
}

<iframe name="sif1" sandbox="allow-forms allow-modals allow-scripts" frameborder="0"></iframe>

We need to write a query by passing single country like ['US'] or multiple country like ['US', 'CA']. Query should only return us matching sub document from promoted titles.

For example, If we pass country as 'US', we should get primary isbn 9781453238431, 9781504063562, 9781497673984 and 9781453274248. If we pass country as ['GB', 'CA'], we need to get 9781453238431, 9781504063562, 9781497673984 and 9780547526959. If we pass ['GB'], we should only get 9781453238431 sub document

We were trying with promotedtitles.countries : {$in : ['US', 'CA']} but that don't work.

Thanks in advance

CodePudding user response：

You can $unwind and $match like this:

• $unwind is to deconstruct the array and get every object as a different value instead all together in an array.
• Then you can filter each object using $match and $in.

db.collection.aggregate([
  {
    "$unwind": "$promotedtitles"
  },
  {
    "$match": {
      "promotedtitles.countries": {
        "$in": [
          "US"
        ]
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "result": "$promotedtitles.primaryisbn"
    }
  }
])

Example here using US and here with GB, CA.

Also, if you want to get all ISBNs in an array you can add a $group like this example