mongodb use aggregate filter multi-level nested array

Question

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 as follow

{
  $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 ^_^

I have tried to use $unwind as follow

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 } }] } } ])

but this command will destroy the structure

I hope keep the original data structure，or there are other ways to restore the structure after using $unwind

周维明 · Accepted Answer · 2023-03-01 01:50:24Z

Refer to the content of this article: enter link description here

I have tried to write codes as follow:

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
                  ]
                }
              }
            }
          }
        },
        
      }
    }
  }
])

and

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
                        ]
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  
])

Both of these codes can get correct results.

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

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Tobok Sitanggang · Accepted Answer · 2023-03-01 05:45:32Z

0

if you wan to keep the original data, you should replica the field before using $unwind. and after data found, remove the the replica

db.runoob.aggregate([
  {
    $addFields: {
      newlist: "$list"
    }
  },
  {
    $unwind: "$newlist"
  },
  {
    $unwind: "$newlist.list"
  },
  {
    "$match": {
      "newlist.list.id": {
        "$in": [
          111
        ]
      }
    }
  },
  {
    "$unset": "newlist"
  }
])

MONGO PLAYGROUND

the operation $match can also be like below:

{
    "$match": {
      "newlist.list.id": 111
    }
}

edited Mar 1, 2023 at 5:45

answered Mar 1, 2023 at 5:40

Tobok Sitanggang

6305 silver badges15 bronze badges

Collectives™ on Stack Overflow

mongodb use aggregate filter multi-level nested array

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related