Retrieve matched document from nested array

Question

I'm trying to collect all objects in a nested array where the field spec equals unknown.

The structure per document is similar to this:

{
    "_id" :"5b1e73786f11e421956023c3",
    "subs" : [ 
        {
            "name" : "subrepo1",
            "files" : [ 
                {
                    "name" : ".....",
                    "spec" : "Unknown"
                }, 
                {
                    "name" : ".....",
                    "spec" : "Unknown"
                }
            ]
        },
        {
            "name" : "subrepo2",
            "files" : [ 
                {
                    "name" : "file2",
                    "spec" : "Unknown"
                }, 
                {
                    "name" : ".....",
                    "spec" : "1234"
                }
            ]
        }
    ]
}

I tried the following but it doesn't work. I'm think this is in the right direction but i'm probably missing something important.

db.col.aggregate([
    {$match: {'subs.files.spec': 'Unknown'}},
    {$project: {
        'subs.files': {$filter: {
            input: '$subs.files',
            //as: 'subs.files',
            cond: {$eq: ['this.spec', 'FunSuite']}
        }},
        //_id: 0
    }}
])

The expected output would be: (so ONLY the files that have spec equals to Unknown (NOT the other ones)

{
    "_id" : "5b1e73786f11e421956023c3",
    "subs" : [ 
        {
            "name" : "subrepo1",
            "files" : [ 
                {
                    "name" : ".....",
                    "spec" : "Unknown"
                }, 
                {
                    "name" : ".....",
                    "spec" : "Unknown"
                }
            ]
        },
        {
            "name" : "subrepo2",
            "files" : [ 
                {
                    "name" : "file2",
                    "spec" : "Unknown"
                }
            ]
        }
    ]
}

What's your expected output? – Mạnh Quyết Nguyễn Jun 12 '18 at 11:51 — Mạnh Quyết Nguyễn, Jun 12 '18 at 11:51
@MạnhQuyếtNguyễn Added the output to make it clear. – Captain Obvious Jun 12 '18 at 12:52 — Captain Obvious, Jun 12 '18 at 12:52

Ashh · Accepted Answer · 2018-06-12T13:25:06.100

You need to use $filter aggregation operator which gives only the matched element from the array and escapes the other elements

db.collection.aggregate([
  {
    $unwind: "$subs"
  },
  {
    $project: {
      "subs.name": "$subs.name",
      "subs.files": {
        $filter: {
          input: "$subs.files",
          as: "file",
          cond: {
            $eq: [
              "$$file.spec",
              "Unknown"
            ]
          }
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      subs: {
        $push: "$subs"
      }
    }
  }
])

Above will give following output

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "subs": [
      {
        "files": [
          {
            "name": ".....",
            "spec": "Unknown"
          },
          {
            "name": ".....",
            "spec": "Unknown"
          }
        ],
        "name": "subrepo1"
      },
      {
        "files": [
          {
            "name": "file2",
            "spec": "Unknown"
          }
        ],
        "name": "subrepo2"
      }
    ]
  }
]

You can check the result here

And if you want to get the fields as in array then remove the $unwind and $replaceRoot stage from the pipeline

db.collection.aggregate([
  {
    $unwind: "$subs"
  },
  {
    $project: {
      "subs.name": "$subs.name",
      "subs.files": {
        $filter: {
          input: "$subs.files",
          as: "file",
          cond: {
            $eq: [
              "$$file.spec",
              "Unknown"
            ]
          }
        }
      }
    }
  },
  {
    $unwind: "$subs.files"
  },
  {
    $replaceRoot: {
      newRoot: "$subs.files"
    }
  }
])

Above will give following output

[
  {
    "name": ".....",
    "spec": "Unknown"
  },
  {
    "name": ".....",
    "spec": "Unknown"
  },
  {
    "name": "file2",
    "spec": "Unknown"
  }
]

This is not the same input data as given in the example, can you change it? (The output is fine) — Captain Obvious, Jun 12 '18 at 12:59
@CaptainObvious I have the changed the input and ouput... Please check it again... — Ashh, Jun 12 '18 at 13:19
it seems to work; thanks. Can you also provide the query for your original output? The output only contains the files {name: ..., spec: ....} because I would like to have that as well. I'll accept if you include that one. — Captain Obvious, Jun 12 '18 at 13:21

karaxuna · Answer 2 · 2018-06-13T04:23:32.033

0

Try this way:

db.col.aggregate([
    {
        $unwind: '$subs'
    },
    {
        $unwind: '$subs.files'
    },
    {
        $match: {
            'subs.files.spec': 'Unknown'
        }
    }
]);

edited Jun 13 '18 at 04:23

answered Jun 12 '18 at 11:53

karaxuna

26,752
13
82
117

This also returns elements in the files array where the spec doesn't equals to unknown, which I don't want (see output) – Captain Obvious Jun 12 '18 at 12:53

Retrieve matched document from nested array

2 Answers2