Get list of child item ids that misses tag of parent in MongoDB

Question

I want to get all items (or better ids of items) that have missing tags.

If a child item misses the tag of its parent it should be outputted.

The documents looks like this:

[
  {
    id: 1,
    tags: [
      "a",
      "b"
    ],
    childs: [
      2,
      3
    ]
  },
  {
    id: 2,
    tags: [
      "a"
    ],
    childs: []
  },
  {
    id: 3,
    tags: [],
    childs: []
  },
  {
    id: 4,
    tags: [
      "c"
    ],
    childs: [
      5
    ]
  },
  {
    id: 5,
    tags: [
      "c"
    ],
    childs: []
  },
  {
    id: 6,
    tags: [
      "b"
    ],
    childs: [
      5
    ]
  },
  {
    id: 7,
    tags: [],
    childs: []
  },
]

Now I want to search by a tag name to get items with missing tag.

The desired result should look like this:

Check for tag "a":

{
   id: 3,
   ...
}
 
or
 
{
   ids: [3]
}

Check for tag "b":

{
   id: 2,
   ...
},
{
   id: 3,
   ...
}

or
 
{
   ids: [2, 3]
}

I tried aggregation with the lookup and pipeline function, but did not got it working.

[{
    $match: {
        tags: "a",
        childs: {
            $ne: []
        }
    }
}, {
    $lookup: {
        from: 'collection1',
        localField: 'childs',
        foreignField: 'id',
        as: 'childs_items',
        pipeline: [{
             $matching : {
                 "tags": {
                     $nin: "a"
                  }
             }
        }]
    }
}]

What would be the best approach?

EDIT: changed document example tags of last two docs to "c" EDIT2: added example data

user_8987128 · Answer

Might be there will be other easy ways but, this is your corrected query,

$match conditions as usual,
$lookup with pipeline, define variable for childs to access inside lookup
$match conditions, childs match or not, tags not equal to specified character
$project to create ids array, using $reduce

db.collection.aggregate([
  {
    $match: {
      tags: "b", // add your search
      childs: { $ne: [] }
    }
  },
  {
    $lookup: {
      from: "collection",
      let: { childs: "$childs" },
      as: "ids",
      pipeline: [
        {
          $match: {
            $expr: { $in: ["$id", "$$childs"] },
            tags: { $ne: "b" } // add your search
          }
        }
      ]
    }
  },
  {
    $project: {
      id: 1,
      ids: {
        $reduce: {
          input: "$ids",
          initialValue: [],
          in: { $concatArrays: ["$$value", ["$$this.id"]] }
        }
      }
    }
  }
])

Playground

Your last edit, you can use $unwind and then $group after $lookup in above example, remove $project stage,

  { $unwind: "$ids" },
  {
    $group: {
      _id: null,
      ids: { $push: "$ids.id" }
    }
  }

Playground

Get list of child item ids that misses tag of parent in MongoDB

1 Answers