Search code examples
mongodbmongodb-queryaggregation-frameworkprojection

In MongoDB, how to fetch items where child array matches with condition and in result any child items that matched with condition be filtered

I have this document:

enter image description here

JSON:

[
  {
    "website": {
      "id": 1,
      "name": "website-1"
    },
    "_id": "64510561da44c5dc5b0e8935",
    "items": [
      {
        "coin": {
          "id": 1,
          "title": "x"
        },
        "price": 53410,
        "_id": "64510561da44c5dc5b0e8936"
      },
      {
        "coin": {
          "id": 2,
          "title": "y"
        },
        "price": 2000,
        "_id": "64510561da44c5dc5b0e8937"
      }
    ]
  },
  {
    "website": {
      "id": 2,
      "name": "website-2"
    },
    "_id": "645105ff86b991ba179b32f7",
    "items": [
      {
        "coin": {
          "id": 1,
          "title": "x"
        },
        "price": 7250,
        "_id": "645105ff86b991ba179b32f8"
      },
      {
        "coin": {
          "id": 3,
          "title": "z"
        },
        "price": 888,
        "_id": "645105ff86b991ba179b32f9"
      },
      {
        "coin": {
          "id": 4,
          "title": "w"
        },
        "price": 444,
        "_id": "645105ff86b991ba179b32fa"
      }
    ]
  },
  {
    "website": {
      "id": 3,
      "name": "website-3"
    },
    "_id": "245105ff86b991ba179b32f7",
    "items": [
      {
        "coin": {
          "id": 4,
          "title": "w"
        },
        "price": 333,
        "_id": "845105ff86b991ba179b32fa"
      }
    ]
  }
]

What I want:

  • Find by websiteId
  • Find by coinId
  • Filter returned items in result: In each record of result if we check the items, every items be included that coin.id is in coinIds array

What I expect as JSON:

[
  {
    "website": {
      "id": 1,
      "name": "website-1"
    },
    "_id": "64510561da44c5dc5b0e8935",
    "items": [
      {
        "coin": {
          "id": 1,
          "title": "x"
        },
        "price": 53410,
        "_id": "64510561da44c5dc5b0e8936"
      },
      {
        "coin": {
          "id": 2,
          "title": "y"
        },
        "price": 2000,
        "_id": "64510561da44c5dc5b0e8937"
      }
    ]
  },
  {
    "website": {
      "id": 2,
      "name": "website-2"
    },
    "_id": "645105ff86b991ba179b32f7",
    "items": [
      {
        "coin": {
          "id": 1,
          "title": "x"
        },
        "price": 7250,
        "_id": "645105ff86b991ba179b32f8"
      }
    ]
  }
  ]

My current code:

const websiteIds = [1,2];
const coinIds = [1,2];

const result = await MyModel.find(
    {
      'website.id': { $in: websiteIds },
      'items.coin.id': { $in: coinIds },
    }
  );

It return correct filtered records by websiteIds but in each record all items of items field be returned if coin.id of one of items be matched with coinIds. I want Items in returned JSON be filtered by coinIds.

Solution

  • Since you want to be able to project more than one item, a simple find with projection will not do the job. One option is to use a pipeline with $filter:

    const result = await MyModel.aggregate([
  {$match: {
      "website.id": {$in: websiteIds},
      "items.coin.id": {$in: coinIds}
  }},
  {$set: {
      items: {$filter: {
          input: "$items",
          cond: {$in: ["$$this.coin.id", coinIds]}
      }}
  }}
])

    See how it works on the playground example