The collection items has item_code, tax_category_code and price.

{'item_code':'A001', 'tax_category_code':'T001', 'price': 98},
{'item_code':'A002', 'tax_category_code':'T002', 'price': 39},
{'item_code':'A003', 'tax_category_code':'T001', 'price': 77},
{'item_code':'A004', 'tax_category_code':'T003', 'price': 52},

The collection tax_schema has status, categories and date. Each category has tax_category_code and percentage.

{'status':'active', 'categories': [ {'tax_category_code':'T001', 'priority': 1, 'percentage': 0.1}, {'tax_category_code':'T002', 'priority': 3, 'percentage': 0.5}, {'tax_category_code':'T003', 'priority': 2, 'percentage': 0.87} ], 'date': '2022-11-24T00:00:00-05:00'},
{'status':'inactive', 'categories': [ {'tax_category_code':'T001', 'priority': 0, 'percentage': 0.08}, {'tax_category_code':'T002', 'priority': 2, 'percentage': 0.42}, {'tax_category_code':'T003', 'priority': 4, 'percentage': 0.74} ], 'date': '2022-06-06T00:00:00-05:00'},
{'status':'inactive', 'categories': [ {'tax_category_code':'T001', 'priority': 0, 'percentage': 0.05}, {'tax_category_code':'T002', 'priority': 0, 'percentage': 0.41}, {'tax_category_code':'T003', 'priority': 0, 'percentage': 0.72} ], 'date': '2022-03-31T00:00:00-05:00'}

I am trying to get as result the list of all items, matching the tax_category_code, considering the tax_schema with status: active, and showing the priority and percentage:

[
{'item_code':'A001', 'tax_category_code':'T001', 'price': 98, {'priority': 1, 'percentage': 0.1} },
{'item_code':'A002', 'tax_category_code':'T002', 'price': 39, {'priority': 3, 'percentage': 0.5} },
{'item_code':'A003', 'tax_category_code':'T001', 'price': 77, {'priority': 1, 'percentage': 0.1}},
{'item_code':'A004', 'tax_category_code':'T003', 'price': 52, {'priority': 2, 'percentage': 0.87}}
]

I have tried this query but I get all tax_schema from on each item:

db.getCollection('items').aggregate([
  {
    $lookup: {
      from: 'tax_schema',
      localField: 'tax_category_code',
      foreignField: 'categories.tax_category_code',
      as: 'tax_schema',
    },
  },
  {
      $project: {
        item_code: 1,
        tax_category_code: 1,
        price: 1,
        tax_schema_category: { $arrayElemAt: ['$tax_schema.categories', 0] },
      }      
  }
])

CodePudding user response：

You cannot directly $lookup categories.tax_category_code, which is resolved as an array field. This will make you fetch all entries in the array if any of them matches. Instead, you can $unwind and $match in the sub-pipeline.

db.items.aggregate([
  {
    "$lookup": {
      "from": "tax_schema",
      let: {
        tcc: "$tax_category_code"
      },
      pipeline: [
        {
          $match: {
            "status": "active"
          }
        },
        {
          "$unwind": "$categories"
        },
        {
          $match: {
            $expr: {
              $eq: [
                "$$tcc",
                "$categories.tax_category_code"
              ]
            }
          }
        }
      ],
      "as": "tax_schema"
    }
  },
  {
    $project: {
      item_code: 1,
      tax_category_code: 1,
      price: 1,
      tax_schema_category: {
        $arrayElemAt: [
          "$tax_schema.categories",
          0
        ]
      },
      
    }
  }
])

Mongo Playground