Query nested array from document-CodePudding

Given the following document data in collection called 'blah'...

[
{
    "_id" : ObjectId("60913f55987438922d5f0db6"),
    "procedureCode" : "code1",
    "description" : "Description 1",
    "coding" : [
        {
            "system" : "ABC",
            "code" : "L111"
        },
        {
            "system" : "DEFG",
            "code" : "S222"
        }
    ]
},
{
    "_id" : ObjectId("60913f55987438922d5f0dbc"),
    "procedureCode" : "code2",
    "description" : "Description 2",
    "coding" : [
        {
            "system" : "ABC",
            "code" : "L999"
        },
        {
            "system" : "DEFG",
            "code" : "X3333"
        }
    ]
}
]

What I want to get is all of the coding elements where system is ABC for all parents, and an array of codes like so.

[
{ "code": "L111" },
{ "code": "L999" },
]

If I use db.getCollection('blah').find({"coding.system": "ABC"}) I get the parent document with any child in the coding array of ICD.

If I use...

db.getCollection("blah")
    .find({ "coding.system": "ABC" })
    .projection({ "coding.code": 1 })

I do get the parent documents which have a child with a system of "ABC", but the coding for "DEFG" seems to come along for the ride too.

{
    "_id" : ObjectId("60913f55987438922d5f0db6"),
    "coding" : [
        {
            "code" : "L989"
        },
        {
            "code" : "S102"
        }
    ]
},
{
    "_id" : ObjectId("60913f55987438922d5f0dbc"),
    "coding" : [
        {
            "code" : "L989"
        },
        {
            "code" : "X382"
        }
    ]
}

I have also tried experimenting with:

 db.getCollection("blah").aggregate(
   { $unwind: "$coding" },
   { $match: { "system": "ICD" } }
 );

.. as per this page: mongoDB query to find the document in nested array ... but go no where fast with that approach. i.e. no records at all.

What query do I need, please, to achieve something like this..?

[
{ "code": "L111" },
{ "code": "L999" },
...
]

or even better, this..?

[
"L111",
"L999",
...
]

CodePudding user response：

db.collection.aggregate([
  {
    $match: { "coding.system": "ABC" }
  },
  {
    $unwind: "$coding"
  },
  {
    $match: { "coding.system": "ABC" }
  },
  {
    $project: { code: "$coding.code" }
  }
])

mongoplayground

db.collection.aggregate([
  {
    $match: { "coding.system": "ABC" }
  },
  {
    $unwind: "$coding"
  },
  {
    $match: { "coding.system": "ABC" }
  },
  {
    $group: {
      _id: null,
      coding: { $push: "$coding.code" }
    }
  }
])

mongoplayground

CodePudding user response：

Instead of $unwind, $match you can also use $filter:

db.collection.aggregate([
  { $match: { "coding.system": "ABC" } },
  {
    $project: {
      coding: {
        $filter: {
          input: "$coding",
          cond: { $eq: [ "$$this.system", "ABC" ] }
        }
      }
    }
  }
])