CODEWITHSUNDEEP
mongodbaggregation-framework
phani
phani

Reputation: 613

Mongodb aggregation to get required objects from array

My sample document is:

{
  "_id": 1,
  "offers": [
    {
      "id": 12345,
      "amount": 100,
      "eligibleProducts": [
        111,
        123,
        156
      ]
    },
    {
      "id": 12346,
      "amount": 100,
      "eligibleProducts": [
        222,
        333
      ]
    }
  ]
}

I want to filter the offers with eligibleProducts.

If my eligibleProducts are [111, 777, 888] then return this:

{
          "id": 12345,
          "amount": 100,
          "eligibleProducts": [
            111,
            123,
            156
          ]
        }

I used $setIsSubset aggregate but this is comparing with a full array,

"Offers" : { "$filter" : { "input" : "$Offers", "as" : "offer", "cond" : { "$and" : [{ "$setIsSubset" : {[111, 777, 888], "$$offer.eligibleProducts"]}}]}}}

Upvotes: 2

Views: 84

Answers (1)

turivishal
turivishal

Reputation: 36134

I used $setIsSubset aggregate but this is comparing with a full array,

The $setIsSubset will not work here because, returns true when the first array is a subset of the second, including when the first array equals the second array, and false otherwise.

You can try below approach,

  • $match are eligibleProducts ids in input to filter main documents
  • $filter to iterate loop of offers array
  • $filter to iterate loop of eligibleProducts array and check is id in input ids
  • $ne to check if the above filter result is not [] empty then return object
db.collection.aggregate([
  {
    $match: {
      "offers.eligibleProducts": {
        $in: [111, 777, 888]
      }
    }
  },
  {
    $project: {
      offers: {
        $filter: {
          input: "$offers",
          cond: {
            $ne: [
              {
                $filter: {
                  input: "$$this.eligibleProducts",
                  cond: { $in: ["$$this", [111, 777, 888]] }
                }
              },
              []
            ]
          }
        }
      }
    }
  }
])

Playground

Upvotes: 1

Related Questions