mongo group and project for nested documents

Question

I am new to mongo I am using mongo 3.6 I have a collection with document structure as:

{
    "_id" : "236165301",
    "boxID" : "431414",    
    "boxName" : "Test",
    "packets" : [ 
        {
            "packetID" : "635665346",
            "packetQty" : "5",
            "packetNum" : "1", 
            "packetItems" : [
                {
                    "packetItemNum" : "Ab89",
                    "packetItemQty" : "1",
                    "packetItemCode" : "6",
                },
                {
                    "packetItemNum" : "Ab90",
                    "packetItemQty" : "3",
                    "packetItemCode" : "6",
                },
                {
                    "packetItemNum" : "Ab87",
                    "packetItemQty" : "3",
                    "packetItemCode" : "8",
                }
            ]
        },
        {
            "packetID" : "635665380",
            "packetQty" : "6",
            "packetNum" : "1", 
            "packetItems" : [
                {
                    "packetItemNum" : "Bc89",
                    "packetItemQty" : "1",
                    "packetItemCode" : "8",
                },
                {
                    "packetItemNum" : "Bc90",
                    "packetItemQty" : "3",
                    "packetItemCode" : "6",
                },
                {
                    "packetItemNum" : "Bc87",
                    "packetItemQty" : "3",
                    "packetItemCode" : "8",
                }
            ]
        }       

    ]
}

My requirement is to filter elements with packetItemCode as 6 without disrupting the structure

Required Output:

{
    "_id" : "236165301",
    "boxID" : "431414",    
    "boxName" : "Test",
    "packets" : [ 
        {
            "packetID" : "635665346",
            "packetQty" : "5",
            "packetNum" : "1", 
            "packetItems" : [
                {
                    "packetItemNum" : "Ab89",
                    "packetItemQty" : "1",
                    "packetItemCode" : "6",
                },
                {
                    "packetItemNum" : "Ab90",
                    "packetItemQty" : "3",
                    "packetItemCode" : "6",
                }
            ]
        },
        {
            "packetID" : "635665380",
            "packetQty" : "6",
            "packetNum" : "1", 
            "packetItems" : [               
                {
                    "packetItemNum" : "Bc90",
                    "packetItemQty" : "3",
                    "packetItemCode" : "6",
                }
            ]
        }       

    ]
}

What i tried :

db.getCollection('PacketData').aggregate([
    { "$match" : { "_id":"236165301" } },
    { "$unwind" : "$packets" },
    { "$unwind" : "$packets.packetItems" },
    { "$match" : { "packets.packetItems.packetItemCode" : "6" }}
])

But i am not able to write a group/project statement to get the desired structure output. Can you please help

use $redact operator as mentioned below. You can also checkout this: stackoverflow.com/questions/49611773/… — Rahul Raj

Rahul Raj Rahul Raj · Accepted Answer · 2018-04-10T06:47:00

Use $redact operator in aggregation framework to restrict data:

db.PacketData.aggregate([
{ "$match" : { "_id":"236165301" } },
{ $redact:{
   $cond:{
          if:{$or:[{$eq:["$packetItemCode","6"]},{$not:"$packetItemCode"}]},
          then:"$$DESCEND",
          else:"$$PRUNE"

        }

    }}

])

And the output is:

/* 1 */
{
"_id" : "236165301",
"boxID" : "431414",
"boxName" : "Test",
"packets" : [ 
    {
        "packetID" : "635665346",
        "packetQty" : "5",
        "packetNum" : "1",
        "packetItems" : [ 
            {
                "packetItemNum" : "Ab89",
                "packetItemQty" : "1",
                "packetItemCode" : "6"
            }, 
            {
                "packetItemNum" : "Ab90",
                "packetItemQty" : "3",
                "packetItemCode" : "6"
            }
        ]
    }, 
    {
        "packetID" : "635665380",
        "packetQty" : "6",
        "packetNum" : "1",
        "packetItems" : [ 
            {
                "packetItemNum" : "Bc90",
                "packetItemQty" : "3",
                "packetItemCode" : "6"
            }
        ]
    }
 ]
}

mongo group and project for nested documents

1 Answers