I'm doing a lookup query but would like to exclude records where the boolean field value within the lookup table is false, is this possible? Here's my current query:

const pipeline = [
                {
                    '$lookup': {
                        'from': 'photos',
                        'localField': '_id',
                        'foreignField': 'postId',
                        'as': 'photoData'
                    }
                }, {
                    '$project': {
                        'createdAt': 1,
                        ...
                        'photoData': {
                            '$cond': {
                            'if': {
                                '$eq': [
                                    false, '$photoData.published'
                                ]
                            },
                            'then': '$$REMOVE',
                            'else': '$photoData'
                            }
                        }
                    }
                }
            ]

This is the photos collection

{
    id:'1234',
    url:'some.url.com',
    published:true
},
{
    id:'4567',
    url:'some.otherurl.com',
    published:false
},

I would like to exclude any records from the photos collection (photoData) lookup where 'published' is set to false. I think using 'project' would be the correct way to do this, but I'm not sure, and I can't seem to find the correct syntax/logic

CodePudding user response：

$lookup returns an array, so '$photoData.published' will also be an array, and will therefore never match false.

You can use $filter to remove the unpublished images from the array like:

{$filter: {
  input: "$photoData",
  as: "photo",
  cond: {$ne:["$$photo.published",false]}
}}
``

CodePudding user response：

You can use $lookup with pipeline to perform joining between collections with sub-queries:

1. Join posts (id) with photo (postId).
2. Match with published: true only.

db.posts.aggregate([
  {
    "$lookup": {
      "from": "photos",
      let: {
        postId: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$postId",
                    "$$postId"
                  ]
                },
                {
                  $eq: [
                    true,
                    "$published"
                  ]
                }
              ]
            }
          }
        },
        
      ],
      "as": "photoData"
    }
  },
  {
    "$project": {
      "createdAt": 1,
      "photoData": 1
    }
  }
])

Sample Mongo Playground