mongodb: how to extract field instead of getting an object with aggregate + $lookup + $project

user_3127828 0
Viewed 1991

I have two contracts.

// UserInfo
{ _id: '1', name: 'Alice' }
{ _id: '2', name: 'Bob' }
{ _id: '3', name: 'Charlie' }

// Application
{ _id: '00', userId: '1', jobId: 'gg' }
{ _id: '01', userId: '1', jobId: 'fb' }
{ _id: '02', userId: '2', jobId: 'ms' }

I want to get:

{ name: 'Alice', jobIds: [ 'gg', 'fb' ] }
{ name: 'Bob', jobIds: [ 'ms' ] }
{ name: 'Charlie', jobIds: [] }

Note that Charlie doesn't have applications.

My current code:

UserInfo.aggregate([
  {
    $lookup: {
      from: 'applications',
      let: { userId: '$_id' },
      pipeline: [
        {
          $match: {
            $expr: { $eq: ['$userId', '$$userId'] }
          }
        },
        {
          $project: { jobId: 1, _id: 0 }
        }
      ],
      as: 'jobIds'
    }
  }
])

I got:

{ _id: '1', name: 'Alice', jobIds: [ { jobId: 'gg' }, { jobId: 'fb' } ] }
{ _id: '2', name: 'Bob', jobIds: [ { jobId: 'ms' } ] }
{ _id: '3', name: 'Charlie', jobIds: [] }

How to get jobIds: [ 'gg', 'fb' ] instead of jobIds: [ { jobId: 'gg' }, { jobId: 'fb' } ]?

Haven't found proper way in Aggregation Pipeline Stages.

mongoose aggregation-framework mongodb-query mongodb
1 Answers

Use $addFields stage at the end of the pipeline.

UserInfo.aggregate([
  { "$lookup": {
    "from": "applications",
    "let": { "userId": '$_id' },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$userId", "$$userId"] }}},
      { "$project": { "jobId": 1, "_id": 0 }}
    ],
    "as": "jobIds"
  }},
  { "$addFields": { "jobIds": "$jobIds.jobId" }}
])
user_75106570
Related