Querying mongo array of embedded documents in aggregation pipeline

Question

I was looking into the different ways of querying on array of embedded documents in aggregation pipeline MongoDB. Looks like MongoDB has less support for this.

Let's say we have following documents in test collection:

/* 1 */
   {
    "_id" : ObjectId("59df2c39fbd406137d4290b3"),
    "a" : 1.0,
    "arr" : [ 
        {
           "key": 1,
           "sn" : "a",
           "org": "A"
        }
    ]
   }

/* 2 */
{
    "_id" : ObjectId("59df2c47fbd406137d4290b4"),
    "a" : 2.0,
    "arr" : [ 
        {
            "sn" : "b",
            "key": 2,
            "org": "B"
        }
    ]
}

/* 3 */
{
    "_id" : ObjectId("59df2c50fbd406137d4290b5"),
    "a" : 3.0,
    "arr" : [ 
        {

           "key": 3,
           "sn" : "c", 
           "org": "C"
        }
    ]
}

/* 4 */
{
    "_id" : ObjectId("59df2c85fbd406137d4290b6"),
    "a" : 1.0,
    "arr" : [ 
        {
           "key": 1,
           "sn" : "a",
           "org": " A"
        }
    ]
}

/* 5 */
{
    "_id" : ObjectId("59df2c9bfbd406137d4290b7"),
    "a" : 3.0,
    "arr" : [ 
        {
            "sn" : "b",
            "key": 2,
        }
    ]
}

/* 6 */
{
    "_id" : ObjectId("59df2e41fbd406137d4290b8"),
    "a" : 4.0,
    "arr" : [ 
        {
            "sn" : "b",
            "key" : 2
        }
    ]
}

/* 7 */
{
    "_id" : ObjectId("59df2e5ffbd406137d4290b9"),
    "a" : 5.0,
    "arr" : [ 
        {
           "key" : 2,
            "sn" : "b"
        }, 
        {
            "sn" : "a",
            "key" : 1
        }
    ]
}

And I wanted to categorize the above documents based on "arr.sn" field value using below query:

db.test.aggregate([{"$addFields": {"Category" : { $switch: {
     branches : [
       { case : { $eq : [ "$arr.nm", "a" ] }, then : "Category 1"}
    ],
    default : "No Category"
    }}}}])

but $eq operator is not giving correct result, if I use the same $eq in find method, it works:

db.test.find({"arr.sn" : "a"})

I am looking at the way to do it with only single field, here in case "arr.sn" field. Is there any way to project the field from embedded documents from the array?

Any help would be appreciated.

s7vr · Accepted Answer · 2017-10-16 14:03:05Z

1

$eq(aggregation) compares both value and type different from query eq opeator which can compare values for any type.

You need $in(aggregation) to verify value in a array.

Something like

[
  {
    "$addFields": {
      "Category": {
        "$switch": {
          "branches": [
            {
              "case": {
                "$in": [
                  "a",
                  "$arr.sn"
                ]
              },
              "then": "Category 1"
            }
          ],
          "default": "No Category"
        }
      }
    }
  }
]

answered Oct 16, 2017 at 14:03

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Sagar Rathod Over a year ago

thanks @Veeram for your solution, actually $in operator requires all array fields to be present, however I am looking at the way to do it with only single field, here in case "arr.sn" field. I have modified the array entries in question. Is there any way to project the field from embedded documents from the array?

s7vr Over a year ago

Np. I'm not following you. What do you mean by single field ? arr.sn returns array of values and $in looks for a value in array of values. What did I miss ?

Sagar Rathod Over a year ago

Your solution works but not in the case where array contains below embedded document For ex: arr : [ { "key": 1, "sn" : "a", "org": " A" } ].

Sagar Rathod Over a year ago

In $in clause, I just want to try with "sn" : "a"

s7vr Over a year ago

It works for me in all cases. Not sure why you want to try sn:a when there is no such syntax in the aggregation query pipeline. In above case arr.sn returns ["a"] and when you do "a" in ["a"] it returns true.

|

Collectives™ on Stack Overflow

Querying mongo array of embedded documents in aggregation pipeline

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related