Use aggregate filter multi-level nested array

weiming_zhou · February 28, 2023, 1:12pm

I have an array：

[
  {
    id: 1,
    name: 1,
    list: [
      {
        id: 11,
        name: 11,
        list: [
          [
            { id: 111, name: 111 },
            { id: 112, name: 112 }
          ]
        ]
      }
    ]
  },
  {
    id: 6,
    name: 6,
    list: [
      {
        id: 62,
        name: 12,
        list: [ [ { id: 111, name: 111 } ] ]
      }
    ]
  }
]

I hope filter the second-level list array，use command

{
  $project: {
    id: 1, name: 1,
    list: {
      id: 1, name: 1,
      list: {
        $filter: {
          input: '$list.list',
          as: 'item',
          cond: { $eq: ['$$item.name', 111] }
        }
      }
    }
  }
}

but not get the expected result. The complete filter code is as follows:

db.runoob.aggregate([{ $match: { $or: [{ 'name': 1, 'list.name': { $eq: 11 } }, { name: 6, 'list.name': { $eq: 12 } }] } }, { $project: { id:1, name: 1, 'list': { $filter: { input: '$list', as: 'item', cond: { $or: [{ $and: [{ $in: ['$$item.id', [11, 12]] }, {$eq: ['$$item.name', 11]}] }, { $and: [{ $in: ['$$item.id', [61, 62]] }, {$eq: ['$$item.name', 12]}] }] } } } } }, { $project: { id: 1, name: 1, list: { id: 1, name: 1, list: { $filter: { input: '$list.list', as: 'item1', cond: { $eq: ['$$item1.name', 111] } } } } } }])

Please help me，thanks

steevej · March 1, 2023, 3:07am

You are almost there. Thanks for the well formatted documents and code. However, different names for list, id and name at different level would make the solution easier to understand.

What you are missing is a $map for the top level list. Then a 2nd $map for the list field that uses your $filter. Your final $project should look like:

{ "$project" : {
    "id" : 1 ,  
    "name" : 1 ,
    "list" : { "$map" : {
        "input" : "$list" ,
        "as" : "top_level_list_element" ,
        "in" : {
            "id" : "$$top_level_list_element.id" ,
            "name" : "$$top_level_list_element.name" ,
            "list" :  { "$map" : {
                "input" : "$$top_level_list_element.list" ,
                "as" : "second_level_list_element" ,
                "in" : { "$filter" : {
                    "input" : "$$second_level_list_element" ,
                    "as" : "final_level_list_element" ,
                    "cond" : { "$eq" : [ "$$final_level_list_element.name" , 111 ] }
                } }
            } }
        }
    } }
} }

weiming_zhou · March 1, 2023, 4:04am

@steevej Thanks a lot.

Today I have tried to use three ways, one of thems is very similar to yours, the three codes are as follows:

use $project + $map + $filter

db.collection.aggregate([
  {
    $match: {
      $or: [
        {
          "name": 1,
          "list.name": {
            $eq: 11
          }
        },
        {
          name: 6,
          "list.name": {
            $eq: 12
          }
        }
      ]
    }
  },
  {
    $project: {
      id: 1,
      name: 1,
      "list": {
        $filter: {
          input: "$list",
          as: "item",
          cond: {
            $or: [
              {
                $and: [
                  {
                    $in: [
                      "$$item.id",
                      [
                        11,
                        12
                      ]
                    ]
                  },
                  {
                    $eq: [
                      "$$item.name",
                      11
                    ]
                  }
                ]
              },
              {
                $and: [
                  {
                    $in: [
                      "$$item.id",
                      [
                        61,
                        62
                      ]
                    ]
                  },
                  {
                    $eq: [
                      "$$item.name",
                      12
                    ]
                  }
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    $project: {
      id: 1,
      name: 1,
      list: {
        $map: {
          input: "$list",
          as: "item1",
          in: {
            id: "$$item1.id",
            name: "$$item1.name",
            list: {
              $filter: {
                input: "$$item1.list",
                as: "item2",
                cond: {
                  $eq: [
                    "$$item2.name",
                    111
                  ]
                }
              }
            }
          }
        },
        
      }
    }
  }
])

use $addFields + $map + $filter

db.collection.aggregate([
  {
    "$addFields": {
      "list": {
        "$map": {
          "input": "$list",
          "as": "a1",
          "in": {
            id: "$$a1.id",
            name: "$$a1.name",
            list: {
              "$map": {
                "input": "$$a1.list",
                "as": "a2",
                "in": {
                  id: "$$a2.id",
                  name: "$$a2.name",
                  list: {
                    "$filter": {
                      "input": "$$a2.list",
                      "as": "a3",
                      "cond": {
                        $eq: [
                          "$$a3.name",
                          1111
                        ]
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  
])

use $unwind + $match: but this command will destroy the structure, I hope keep the original data structure,
can I use $group or other commands to restore the original structure? Or do I finish restoring the structure in my application code?

db.runoob.aggregate([
  { $unwind: '$list' },
  { $unwind: '$list.list' },
  { $unwind: '$list.list.list' },
  {
    $match: {
      $or: [
        {
          'name': 1,
          'list.name': { $eq: 11 },
          'list.list.name': { $eq: 112 }
        },
        {
          name: 6,
          'list.name': { $eq: 12 }
        }]
    }
  }]);

Because my array nesting level will be very deep, whether there are other better options?

thanks again

steevej · March 1, 2023, 1:05pm

Your code does not match the sample documents you shared.

Your code

weiming_zhou:

    list: {
        $map: {
          input: "$list",
          as: "item1",
          in: {
            id: "$item1.id",
            name: "$item1.name",
            list: {
              $filter: {
                input: "$item1.list",

is missing the fact that list.list is not the list you want to filter. You want to filter each elements of list.list because it is a list of list.

Your other code:

is wrong in relation to the sample documents and produces no documents. You do not have 3 level of list named list. You have 2. The last level of list has not name as you can see when I perform the 2 first $unwind on your sample data.

{ _id: ObjectId("63feb634f908e96a872caa32"),
  id: 1,
  name: 1,
  list: 
   { id: 11,
     name: 11,
     list: [ { id: 111, name: 111 }, { id: 112, name: 112 } ] } }
{ _id: ObjectId("63feb634f908e96a872caa33"),
  id: 6,
  name: 6,
  list: { id: 62, name: 12, list: [ { id: 111, name: 111 } ] } }

The following $unwind series

[
  { $unwind: '$list' },
  { $unwind: '$list.list' },
  { $unwind: '$list.list' }

produce a better result on your sample documents with:

{ _id: ObjectId("63feb634f908e96a872caa32"),
  id: 1,
  name: 1,
  list: { id: 11, name: 11, list: { id: 111, name: 111 } } }
{ _id: ObjectId("63feb634f908e96a872caa32"),
  id: 1,
  name: 1,
  list: { id: 11, name: 11, list: { id: 112, name: 112 } } }
{ _id: ObjectId("63feb634f908e96a872caa33"),
  id: 6,
  name: 6,
  list: { id: 62, name: 12, list: { id: 111, name: 111 } } }

Yes you may but it is not recommended. I remember that @Asya_Kamsky wrote about it but I could not locate her post.

Yes you may but you will have a lot of duplicated data (from the top level to the second to last due to $unwind) to transfer.

May be. I provided the best I know based on your sample documents.

weiming_zhou · March 2, 2023, 5:00am

I apologize for my mistake, because my subsequent tests use more complex data structures, and part of the code only performs simple tests on local data and specific operators to calculate my ideas.
The data as follows:

@steevej Thank you very much for your patience and serious reply, I think this question has been perfectly answered, And let me learn a lot.

system · March 7, 2023, 5:01am

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.