Aggregation: Return documents based on fields in a subdocument

Working with Data Node.js Frameworks
, ,
1

I’m using an aggregation to return data via a lookup to build the links between documents.

At the moment, the linking is working when User A creates links between their own Assets. But if User A is viewing an Asset that’s been shared with them by User B and navigates to one that has a link to an Asset that hasn’t been shared, I don’t need to return those documents.

The data for a Link is:

{
  "_id": {
    "$oid": "63769c377615fe4cdb4995a6"
  },
  "userId": "620920aa9ddac2074a50472f",
  "toAsset": {
    "$oid": "63769c117615fe4cdb499515"
  },
  "fromAsset": {
    "$oid": "63769c067615fe4cdb4994d9"
  },
  "comment": "<p>Linking of Note 0001 to Note 0002.</p>",
  "createdAt": {
    "$date": {
      "$numberLong": "1668717623761"
    }
  },
  "updatedAt": {
    "$date": {
      "$numberLong": "1668717623761"
    }
  },
  "isEmbedded": false,
  "isActive": true,
  "__v": 0
}

The data for an Asset, as in toAsset and fromAsset, is:

{
  "_id": {
    "$oid": "6377a8d834671794449f0dca"
  },
  "userId": "636b73f31527830f7bd7a47e",
  "folderId": "636b73f31527830f7bd7a482",
  "title": "Note that hasn't been shared",
  "note": "<p>Here's a Note that hasn't been shared.</p>",
  "typeOfAsset": "note",
  "createdAt": {
    "$date": {
      "$numberLong": "1668786392389"
    }
  },
  "updatedAt": {
    "$date": {
      "$numberLong": "1668786392389"
    }
  },
  "isActive": 3,
  "meta": [...],
  "preferences": [...],
  "sequence": 1,
  "tags": [],
  "attributes": [
    {
      "$oid": "6377a8d834671794449f0dc8"
    }
  ],
  "__v": 0
}

I’m using Attributes to manage what Assets have been shared with whom, and the data is:

{
  "_id": {
    "$oid": "6377a8d834671794449f0dc8"
  },
  "userId": "636b73f31527830f7bd7a47e",
  "numberOfViews": 2,
  "isFavourite": false,
  "isToRead": false,
  "typeOfAccess": "isOwner",
  "sharing": {
    "typeOfShare": "withUsers",
    "sharedWith": [],
    "segementsForUrl": []
  },
  "__v": 0
}

Now, the task here is to somehow how return the Assets that have been shared, but after a bunch of different attempts (as per the code that’s been commented out), I’ve so far failed.

The code is:

const project = {
  $project: {
    _id: 0,
    id: '$_id',
    userId: 1,
    [directionOfLink]: 1,
    // attributes: {
    //   $filter: {
    //     input: '$assets',
    //     as: 'assets',
    //     cond: {
    //       $and: [
    //         { $in: [ '$$assets.attributes.typeOfAccess', ['isOwner', 'asAuthor', 'asReader'] ] },
    //         { $eq: [ '$$assets.attributes.userId', context.body.variables.userId ] }
    //       ]
    //     }
    //   }
    // },
    comment: 1,
    createdAt: 1,
    updatedAt: 1,
    isActive: 1,
    score: {
      $meta: 'searchScore'
    }
  }
}

const lookup = {
  $lookup: {
    from: 'assets',
    localField: directionOfLink,
    foreignField: '_id',
    // pipeline: [{
    //   $match: {
    //     'attributes.userId': context.body.variables.userId,
    //     $expr: { $in: [ 'attributes.typeOfAccess', ['isOwner', 'asAuthor', 'asReader'] ] }
    //   }
    // }],
    as: directionOfLink
  }
}

const addFields = {
  $addFields: {
    something: {
      $filter: {
        input: '$assets',
        cond: {
          $and: [
            { $eq: [ '$$this.attributes.typeOfAccess', ['isOwner', 'asAuthor', 'asReader'] ] },
            { $eq: [ '$$this.attributes.userId', context.body.variables.userId ] }
          ]
        }
      }
    }
  }
}

const match = {
  $match: {
    [args.directionOfLink]: new mongoose.Types.ObjectId(args.assetId)
  }
}

Any thoughts would be appreciated.

2

Anyone have thoughts on this? The project has stalled at the moment because of this problem.

3

Hello @Wayne_Smallman,

Your question is not clear to me, Are there 2 collections or a single? can you please provide example documents and the expected result as per those documents, and also show your external input values?

I can see you have assigned stages in variables, Can you show how did you execute the final query?

4

While I admit it’s possible this isn’t the most efficient approach (I’m no expert), it’s at least working:

const lookup = {
  $lookup: {
    from: 'assets',
    localField: directionOfLink,
    foreignField: '_id',
    as: directionOfLink,
    pipeline: [
      {
        $lookup: {
          from: 'assets_attributes',
          as: 'attributesInAssets',
          pipeline: [
            {
              $match: {
                $expr: {
                  $and: [
                    { $eq: [ '$userId', context.body.variables.userId ] },
                    { $in: [ '$typeOfAccess', ['isOwner', 'asAuthor', 'asReader'] ] },
                  ]
                }
              }
            }
          ]
        }
      },
      {
        $unwind: '$attributesInAssets'
      },
      {
        $match: {
          $expr: {
            $in: [ '$attributesInAssets._id', '$attributes' ]
          }
        }
      },
      {
        $group: {
          _id: '$_id',
          userId: { $first: '$userId' },
          folderId: { $first: '$folderId' },
          title: { $first: '$title' },
          typeOfAsset: { $first: '$typeOfAsset' },
          createdAt: { $first: '$createdAt' },
          updatedAt: { $first: '$updatedAt' },
          isActive: { $first: '$isActive' },
          attributes: { $first: '$attributes' },
          attributesInAssets: {
            $push: '$attributesInAssets._id'
          }
        }
      },
      {
        $project: {
          _id: 1,
          userId: 1,
          folderId: 1,
          title: 1,
          typeOfAsset: 1,
          attributes: 1,
          attributesInAssets: 1,
          createdAt: 1,
          updatedAt: 1,
          isActive: 1
        }
      }
    ]
  }
}

const redact = {
  $redact: {
    $cond: {
      if: {
        $gt: [ {
          $size: `$${directionOfLink}`
        }, 0 ]
      },
      then: '$$KEEP',
      else: '$$PRUNE'
    }
  }
}