MongoDB聚合$lookup实现：卡片过滤器关联与结构转换需求

需求 #

实现MongoDB聚合查询，最终返回指定卡片文档，其原字符串类型的filters数组需替换为来自filters集合的指定结构对象数组。

集合结构说明 #

• cards集合：文档包含cardId与字符串数组类型的filters，数组元素为子过滤器的value值；
• filters集合：主过滤器文档包含enabled、archived字段，以及子过滤器列表list，list中每个元素包含label、value、disabled字段。

示例数据 #

{
  "cards": [
    {
      "cardId": "one-two-three",
      "filters": [
        "one",
        "two",
        "five"
      ]
    }
  ],
  "filters": [
    {
      "label": "filter-one-primary",
      "enabled": true,
      "archived": false,
      "list": [
        {
          "label": "One",
          "value": "one",
          "disabled": false
        },
        {
          "label": "Four",
          "value": "four",
          "disabled": false
        }
      ]
    },
    {
      "label": "filter-two-primary",
      "enabled": true,
      "archived": false,
      "list": [
        {
          "label": "Two",
          "value": "two",
          "disabled": false
        },
        {
          "label": "Five",
          "value": "five",
          "disabled": false
        }
      ]
    }
  ]
}

查询逻辑要求 #

1. 筛选出满足enabled: true且archived: false，且list中包含卡片filters数组内任意值的主过滤器；
2. 将关联到的子过滤器转换为如下结构，替换原卡片的filters数组：

[ 
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "cardId": "one-two-three",
    "filters": [
        {
            "primary": "filter-one-primary", // 来自主过滤器的label字段
            "secondary": "One", // 来自子过滤器的label字段
            "id": "one", // 来自子过滤器的value字段
            "disabled": false // 来自子过滤器的disabled字段
        },
        {
            "primary": "filter-two-primary",
            "secondary": "Two",
            "id": "two",
            "disabled": false
        },
        {
            "primary": "filter-two-primary",
            "secondary": "Five",
            "id": "five",
            "disabled": false
        }
    ]
  }
]

当前待修正的聚合管道 #

// DATA
db={
  "cards": [
    {
      "cardId": "one-two-three",
      "filters": [
        "four",
        "five",
        "six"
      ]
    }
  ],
  "filters": [
    {
      "label": "filter-one-primary",
      "enabled": true,
      "archived": false,
      "list": [
        {
          "label": "One",
          "value": "one",
          "disabled": false
        },
        {
          "label": "Four",
          "value": "four",
          "disabled": false
        }
      ]
    },
    {
      "label": "filter-two-primary",
      "enabled": true,
      "archived": false,
      "list": [
        {
          "label": "Two",
          "value": "two",
          "disabled": false
        },
        {
          "label": "Five",
          "value": "five",
          "disabled": false
        }
      ]
    },
    {
      "label": "filter-three-primary",
      "enabled": true,
      "archived": false,
      "list": [
        {
          "label": "Three",
          "value": "three",
          "disabled": false
        },
        {
          "label": "SiX",
          "value": "six",
          "disabled": false
        }
      ]
    }
  ]
}

db.cards.aggregate([
  {
    $match: {
      "cardId": "one-two-three"
    }
  },
  {
    $lookup: {
      from: "filters",
      "let": {
        "id": "filters"
      },
      pipeline: [
        {
          $match: {
            $and: [
              {
                $expr: {
                  $in: [
                    "$$id",
                    "$list.value"
                  ]
                },
                "enabled": true,
                "archived": false
              }
            ]
          }
        }
      ],
      as: "filters"
    }
  }
])

修正后的聚合管道 #

db.cards.aggregate([
  // 匹配目标卡片
  { $match: { "cardId": "one-two-three" } },
  // 关联filters集合，筛选符合条件的主过滤器并转换子项结构
  {
    $lookup: {
      from: "filters",
      let: { cardFilters: "$filters" },
      pipeline: [
        {
          $match: {
            enabled: true,
            archived: false,
            // 筛选list中存在卡片filters值的主过滤器
            $expr: { $gt: [{ $size: { $setIntersection: ["$list.value", "$$cardFilters"] } }, 0] }
          }
        },
        // 展开主过滤器的list数组
        { $unwind: "$list" },
        // 只保留和卡片filters匹配的子过滤器
        {
          $match: {
            $expr: { $in: ["$list.value", "$$cardFilters"] }
          }
        },
        // 转换为目标结构
        {
          $project: {
            _id: 0,
            primary: "$label",
            secondary: "$list.label",
            id: "$list.value",
            disabled: "$list.disabled"
          }
        }
      ],
      as: "matchedFilters"
    }
  },
  // 替换原filters数组为转换后的结构
  {
    $project: {
      cardId: 1,
      filters: "$matchedFilters"
    }
  }
])

修正说明 #

1. 关联逻辑修正：原$lookup中let变量赋值错误，改为绑定卡片的filters数组；用$setIntersection判断主过滤器的子项值与卡片filters是否有交集，精准筛选符合条件的主过滤器。
2. 子项匹配与转换：通过$unwind展开主过滤器的list数组，再匹配和卡片filters一致的子项，最后用$project将子项映射为要求的结构。
3. 替换原数组：最后用$project将原filters字段替换为转换后的结果数组。

内容的提问来源于stack exchange，提问作者SoEzPz