Query nested array from document

Question

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",
...
]

YuTing · Accepted Answer · 2022-03-15 09:10:36Z

1

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

answered Mar 15, 2022 at 9:10

YuTing

6,6692 gold badges8 silver badges19 bronze badges

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

2 Comments

Dib Over a year ago

-Both work as a solution, thank you, but the second is perfect as it is just an array of values. Now I need to work out how to make this distinct!

YuTing Over a year ago

@Dib change $push into $addToSet can make this distinct

Wernfried Domscheit · Accepted Answer · 2022-03-15 09:45:54Z

1

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" ] }
        }
      }
    }
  }
])

answered Mar 15, 2022 at 9:45

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

2 Comments

Dib Over a year ago

Excellent thank you. Does $$this take the scope of each object in the projection?

Wernfried Domscheit Over a year ago

Yes, it is the default. You can define your own, if you prefer, for example {$filter: { input: "$coding", as: "code", cond: { $eq: [ "$$code.system", "ABC" ] }}}

Collectives™ on Stack Overflow

Query nested array from document

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related