Mongodb - aggregate items from array, matching item property or subarray item property

Question

I have documents with the following structure:

{
  _id: "UNIQUE_ID",
  myarray: [
    {
      mykey: '12345',
      // other fields
    },
    {
      // other fields
      nestedarray: [
        {
          mykey: '67890',
          // other fields
        }
      ]
    }
  ]
}

And I need to return all items from myarray on which mykey (on items of myarray or nestedarray) belongs to a set of values. For example, for the document above, if the set of values is ['12345, '67890'], both items from myarray should be returned.

I'm using the following code to do that:

collection.aggregate([
  {
    $match: {
      "_id": documentId,
      $or: [
        { "myarray": {$elemMatch: {"mykey": { $in: ['12345, '67890'] } } } },
        { "myarray.$.nestedarray": {$elemMatch: {"mykey": { $in: ['12345, '67890'] } }} }
      ]
    }
  },
  {
    $project: {
      myarray: {
        $filter: {
          input: '$myarray',
          as: 'arrayitem',
          cond: {
            $or: [
              { $eq: ["$$arrayitem.mykey", '12345'] },
              { $eq: ["$$arrayitem.nestedarray.[$].mykey", '12345'] }
            ]
          }
        }
      }
    }
  }
]);

But this only return the items on which mykey matches at myarray level (not matching it inside nestedarray).

What am I doing wrong?

Also, how may I use set ['12345, '67890'] instead of single value '12345' inside of $filter function?

Clarifying:

If mykey match is on an item from myarray: include this item (this item will not have a nestedarray field)
If mykey match is on an item from nestedarray: include the item from myarray which contains nestedarray (also with full contents of nestedarray). This item from myarray will not have a mykey field

Example:

Data:

{
  _id: "UNIQUE_ID",
  myarray: [
    {
      mykey: '11111',
      // other fields
    },
    {
      // other fields
      nestedarray: [
        {
          mykey: '22222',
          // other fields
        },
        {
          mykey: '84325',
          // other fields
        }
      ]
    },
    {
      mykey: '645644',
      // other fields
    },
    {
      // other fields
      nestedarray: [
        {
          mykey: '23242',
          // other fields
        },
        {
          mykey: '23433',
          // other fields
        }
      ]
    }
  ]
}

Set of values: ['11111', '22222']

Expected query result:

{
  _id: "UNIQUE_ID",
  myarray: [
    {
      mykey: '11111',
      // other fields
    },
    {
      // other fields
      nestedarray: [
        {
          mykey: '22222',
          // other fields
        },
        {
          mykey: '84325',
          // other fields
        }
      ]
    }
  ]
}

What if mykey exists in nestedarray but not in myArray? — Ashh
– Ashh, Commented Apr 9, 2019 at 18:25
Then the item from myarray which contains nestedarray should be included, and only the matching item(s) from nestedarray should be included — GCSDC
– GCSDC, Commented Apr 9, 2019 at 18:38
@AnthonyWinzlet actually all items from nestedarray will be required (even if only one of them matches). — GCSDC
– GCSDC, Commented Apr 9, 2019 at 19:11
So basically you don't want to filter the nestedarray array? — Ashh
– Ashh, Commented Apr 9, 2019 at 19:14

mickl · Accepted Answer · 2019-04-10 05:22:14Z

2

You can use single $filter and then as cond you can either directly check mykey or use $anyElementTrue for an array.

db.col.aggregate([
    {
        $project: {
            myarray: {
                $filter: {
                    input: "$myarray",
                    cond: {
                        $or: [
                            { $in: [ "$$this.mykey", ["11111", "22222"] ] },
                            { $anyElementTrue: [ 
                                { 
                                    $map: { 
                                        input: { $ifNull: [ "$$this.nestedarray", [] ] }, 
                                        as: "na", 
                                        in: { $in: [ "$$na.mykey", ["11111", "22222"] ] } 
                                    } 
                                } 
                                ] 
                            }
                        ]
                    }
                }
            }
        }
    }
])

Mongo playground

edited Apr 10, 2019 at 5:22

answered Apr 9, 2019 at 18:36

mickl

50.1k9 gold badges71 silver badges99 bronze badges

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

3 Comments

GCSDC Over a year ago

Thanks for your answer! Tried it, but this only returned the item with the non-nested mykey.

mickl Over a year ago

@GCSDC that was a bit unclear to me, modified (and simplified) my answer, works fine with your example

GCSDC Over a year ago

Thats what I was looking for! Thank you very much!

Ashh · Accepted Answer · 2019-04-09 19:51:37Z

2

You can use below aggregation

db.collection.aggregate([
  { "$match": { "_id": documentId }},
  { "$project": {
    "myarray": {
      "$filter": {
        "input": {
          "$map": {
            "input": "$myarray",
            "as": "arrayitem",
            "in": {
              "mykey": "$$arrayitem.mykey",
              "nestedarray": "$$arrayitem.nestedarray",
              "aaaa": {
                "$filter": {
                  "input": "$$arrayitem.nestedarray",
                  "as": "vv",
                  "cond": { "$in": ["$$vv.mykey", ["12345", "67890"]] }
                }
              }
            }
          }
        },
        "as": "ff",
        "cond": {
          "$or": [
            { "$in": ["$$ff.mykey", ["12345", "67890"]] },
            { "$gte": [{ "$size": { "$ifNull": ["$$ff.aaaa", []] }}, 1] }
          ]
        }
      }
    }
  }},
  { "$project": { "myarray.aaaa": 0 }}
])

Here is the working example

edited Apr 9, 2019 at 19:51

answered Apr 9, 2019 at 18:28

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

7 Comments

GCSDC Over a year ago

Thanks for your answer! In this case, should I keep $match exactly as I posted on my question, or should I drop it (in this case, how to match document _id)?

Ashh Over a year ago

Use the single $match with _id. Rest of the thing $filter will do.

GCSDC Over a year ago

Tried it, but I'm getting the following error errmsg:"The argument to $size must be an array, but was of type: null"

Ashh Over a year ago

Updated my answer again

GCSDC Over a year ago

Almost it. The only issue is that it is not bringing the other properties for myarray items (only mykey or nestedarray). I'm trying to figure out how to do that.

|

Collectives™ on Stack Overflow

Mongodb - aggregate items from array, matching item property or subarray item property

2 Answers 2

3 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related