Aggregation: Multi-level filters within $addField

Working with Data
, ,
1

Collection in the database:

{
  "value": {
    "shipmentId": 1079,
    "customer_orders": [
      {
        "customer_order_id": 1124,
        "active": false
      },
      {
        "customer_order_id": 1277,
        "active": true,
        "items": [
          {
            "item_id": 281,
            "active": false,
            "qty": 1,
            "name": "apples",
            "attributes": null
          },
          {
            "item_id": 282,
            "active": true,
            "qty": 2,
            "name": "bananas"
          }
        ]
      }
    ],
    "carrier_orders": [
      {
        "carrier_order_id": 744,
        "active": true
      }
    ]
  }
}

Query:

db.getCollection('shipments').aggregate([
  {
    "$match": {
      "value.shipmentId": {
        "$in": [
          1079
        ]
      }"
    }
  },
  {
    "$project": {
      "value.shipmentId": 1,
      "value.customer_orders": 1,
      "value.carrier_orders": 1,
    }
  },
  {
      "$addFields":{
          "value.customer_orders":{
              $filter:{
                    input: "$value.customer_orders",
                    as: "customer_order",
                    cond: {
                        $eq: ["$$customer_order.active", true]
                    }
              }
          },
          "value.customer_orders.items":{
              $filter:{
                    input: "$value.customer_orders.items",
                    as: "item",
                    cond: {
                        $eq: ["$$item.active", true]
                    }
              }
          },
          "value.carrier_orders": {
              $filter:{
                    input: "$value.carrier_orders",
                    as: "carrier_order",
                    cond: {
                        $eq: ["$$carrier_order.active", true]
                    }
              }
          }
      }
  }
]
);

Desired output:

{
  "value": {
    "shipmentId": 1079,
    "customer_orders": [
      {
        "customer_order_id": 1277,
        "active": true,
        "items": [
          {
            "item_id": 282,
            "active": true,
            "qty": 2,
            "name": "bananas"
          }
        ]
      }
    ],
    "carrier_orders": [
      {
        "carrier_order_id": 744,
        "active": true
      }
    ]
  }
}

I am trying to apply filters at two different levels:

  • “value.customer_orders”
  • “value.customer_orders.items”

What I want is to filter out inactive customer orders, and within active customer orders, filter out inactive items. While doing this, if there are any attributes at the customer order level, we want to retain them too in the output.
How can I achieve this multi-level nesting of conditions and retain attributes using the aggregate pipeline?

2

If do not know but you probably can do it in a single $addField but it would be easier to do it in different stages.

The first $addField will filter out customer_orders with active:true like you do now.

Then a second $addField will $map customer_orders applying the $filter on the inner items.

Use at your own risk:

top_filter = { "$addFields" : {
    "value.customer_orders": { "$filter" : {
        input: "$value.customer_orders",
        as: "customer_order",
        cond: { $eq: ["$$customer_order.active", true] }
    } }
} }

inner_filter = { $addFields : {
    "value.customer_orders" : { $map : {
        input: "$value.customer_orders",
        as: "customer_order",
        in: { $mergeObjects : [
            "$$customer_order" ,
            { "items" : { "$filter" : {
                input: "$$customer_order.items",
                as: "item",
                cond: {  $eq: ["$$item.active", true]  }
            } } }
        ] } 
    } }
} }
3 Likes
3

Wow @steevej this is really good and it works indeed!

I spent a few hours yesterday trying to figure this out and I was missing the $mergeObjects part.
My pipeline was working for the given example by as I suspected, I was actually duplicating the same sub-array of items everywhere :sob: and I couldn’t find a solution just yet.

I love your solution!

Just for the sake of it and because I spent some time on it I’ll just provide the output of my console as I tested everything again with a more “complex” example that was breaking my pipeline. But all the credits is for @steevej!

Very well done!

I my example I added some pears and pizzas to make sure I wasn’t duplicating the same sub-array.

test:PRIMARY> db.coll.findOne()
{
	"_id" : ObjectId("639305dd6b130c4eb3fc8983"),
	"value" : {
		"shipmentId" : 1079,
		"customer_orders" : [
			{
				"customer_order_id" : 1124,
				"active" : false
			},
			{
				"customer_order_id" : 1277,
				"active" : true,
				"items" : [
					{
						"item_id" : 281,
						"active" : false,
						"qty" : 1,
						"name" : "apples",
						"attributes" : null
					},
					{
						"item_id" : 282,
						"active" : true,
						"qty" : 2,
						"name" : "bananas"
					}
				]
			},
			{
				"customer_order_id" : 1234,
				"active" : true,
				"items" : [
					{
						"item_id" : 302,
						"active" : false,
						"qty" : 1,
						"name" : "pears",
						"attributes" : null
					},
					{
						"item_id" : 303,
						"active" : true,
						"qty" : 2,
						"name" : "pizzas"
					}
				]
			}
		],
		"carrier_orders" : [
			{
				"carrier_order_id" : 744,
				"active" : true
			}
		]
	}
}

Then I need the 3 stages of the pipeline:

match = { "$match" : { "value.shipmentId" : { "$in" : [ 1079 ] } } }

filter1 = {
	"$addFields" : {
		"value.customer_orders" : {
			"$filter" : {
				"input" : "$value.customer_orders",
				"as" : "customer_order",
				"cond" : {
					"$eq" : [
						"$$customer_order.active",
						true
					]
				}
			}
		},
		"value.carrier_orders" : {
			"$filter" : {
				"input" : "$value.carrier_orders",
				"as" : "carrier_order",
				"cond" : {
					"$eq" : [
						"$$carrier_order.active",
						true
					]
				}
			}
		}
	}
}

filter2 = {
	"$addFields" : {
		"value.customer_orders" : {
			"$map" : {
				"input" : "$value.customer_orders",
				"as" : "customer_order",
				"in" : {
					"$mergeObjects" : [
						"$$customer_order",
						{
							"items" : {
								"$filter" : {
									"input" : "$$customer_order.items",
									"as" : "item",
									"cond" : {
										"$eq" : [
											"$$item.active",
											true
										]
									}
								}
							}
						}
					]
				}
			}
		}
	}
}

And finally I can aggregate:

test:PRIMARY> db.coll.aggregate([match, filter1, filter2]).pretty()
{
	"_id" : ObjectId("639305dd6b130c4eb3fc8983"),
	"value" : {
		"shipmentId" : 1079,
		"customer_orders" : [
			{
				"customer_order_id" : 1277,
				"active" : true,
				"items" : [
					{
						"item_id" : 282,
						"active" : true,
						"qty" : 2,
						"name" : "bananas"
					}
				]
			},
			{
				"customer_order_id" : 1234,
				"active" : true,
				"items" : [
					{
						"item_id" : 303,
						"active" : true,
						"qty" : 2,
						"name" : "pizzas"
					}
				]
			}
		],
		"carrier_orders" : [
			{
				"carrier_order_id" : 744,
				"active" : true
			}
		]
	}
}

Cheers,
Maxime.

1 Like
4

Thanks for the good words.

And thanks for putting out the whole solution together. I had left the $match and $filter of carrier_orders out because they were not necessary to understand the multi-level filtering. But it is nice to see it all together.

2 Likes
5

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