I have a complex query requiring a chain of nested unwinds and grouping them in order. here are relations between models [policy, asset, assetType, field, fieldType]

• policy has many asset
• asset has one assetType
• asset has many fields
• field has one fieldType

example object would be something like, where

    {
      policy: {
        ..., // policy fields
        assets: [
          {
            ..., // asset fields
            assetType: {},
            fields: [
              {
                ..., // field fields
                fieldType: {},
              },
            ],
          },
        ],
      },
    } 

Now I'm trying to do a pipeline to get the nested date with the same structure above this is the far I get to

    mongoose.model('policy').aggregate([
      {
        $lookup: {
          from: 'assets',
          localField: 'assets',
          foreignField: '_id',
          as: 'assets',
        },
      },
      {
        $lookup: {
          from: 'assettypes',
          let: {
            id: '$assets._id',
            fields: '$assets.fields',
            name: '$assets.displayName',
            atId: '$assets.assetType',
          },
          pipeline: [
            {
              $match: {
                $expr: {
                  $eq: ['$_id', '$$atId'],
                },
              },
            },
            {
              $project: {
                _id: '$$id',
                assetId: '$$id',
                assetDisplayName: '$$name',
                assetFields: '$$fields',
                type: 1,
                name: 1,
              },
            },
          ],
          as: 'assets',
        },
      },
      {
        $unwind: {
          path: '$assets',
        },
      },
      {
        $unwind: {
          path: '$assets.fields',
        },
      },
      {
        $lookup: {
          from: 'fieldtypes',
          let: {
            ftId: '$assets.fields.fieldType',
            value: '$assets.fields.value',
            ref: '$assets._id',
          },
          pipeline: [
            {
              $match: {
                $expr: {
                  $eq: ['$_id', '$$ftId'],
                },
              },
            },
            {
              $addFields: {
                value: '$$value',
                assetId: '$$ref',
              },
            },
          ],
          as: 'assets.fields',
        },
      },
    ]) 

and now I'm stuck with grouping the results to get the optimal object I described above.

Can you help, please?

UPDATE: here is Sample data

CodePudding user response：

If I understand you correctly, you want something like this:

1. Get all the relevant assets from the policies and unwind them (I guess you only want it for few selected policies, otherwise, if you want to use all assets, you may as well start from their collection and in the end group them by policy)
2. Get all the wanted data from other collections. Create a fieldtypes array in each document
3. In order to match each item in fields with its fieldtype use $map with $mergeObjects (this is the more complicated part).
4. Group by policy

db.policies.aggregate([
  {$lookup: {
      from: "assets",
      localField: "assets",
      foreignField: "_id",
      as: "assets"
  }},
  {$unwind: "$assets"},
  {$lookup: {
      from: "fields",
      localField: "assets.fields",
      foreignField: "_id",
      as: "assets.fields"
  }},
  {$lookup: {
      from: "assettypes",
      localField: "assets.assetType",
      foreignField: "_id",
      as: "assets.assetType"
  }},
  {$lookup: {
      from: "fieldtypes",
      localField: "assets.fields.fieldType",
      foreignField: "_id",
      as: "assets.fieldtypes"
  }},
  {$set: {
      "assets.assetType": {$first: "$assets.assetType"},
      "assets.fields": {
        $map: {
          input: "$assets.fields",
          in: {
            $mergeObjects: [
              "$$this",
              {fieldType: {
                  $getField: {
                    input: {
                      $arrayElemAt: [
                        "$assets.fieldtypes",
                        {$indexOfArray: ["$assets.fieldtypes._id", "$$this.fieldType"]}
                      ]
                    },
                    field: "key"
                  }
                }
              }
            ]
          }
        }
      },
      "assets.fieldtypes": "$$REMOVE"
    }
  },
  {$group: {_id: "$_id", assets: {$push: "$assets"}}}
])

See how it works on the playground example