how to find records in nested document in mongodb

Question

Here's sample records of my_list collection, which contains multi-level nested documents, how to find the corresponding "key" value from the record that contains 3rd_party_setting sub-record and with 3rd_party_id="123456" ? i.e. find the key value with corresponding 3rd_party_setting.3rd_party_id ="123456"

[
  {
    "_id": 551,
    "name": "demo1",
    "groups": {
      "123": {
        "name": "group1",
        "teams": {
          "110": {
            "name": "team1",
            "team_id": "2322"
          },
          "111": {
            "name": "team1",
            "team_id": "2322"
          }
        },
        "3rd_party_setting": {
          "3rd_party_id": "123456",
          "key": "this is the key",
          "create_dt": "2020-02-06 01:27:19",
          "update_dt": "2020-02-06 01:27:19"
        }
      }
    }
  },
  {
    "_id": 552,
    "name": "demo2",
    "groups": {
      "124": {
        "name": "group2",
        "teams": {
          "210": {
            "name": "team1",
            "team_id": "2322"
          },
          "211": {
            "name": "team1",
            "team_id": "2322"
          }
        },
        "3rd_party_setting": {
          "3rd_party_id": "123458",
          "key": "this is the key2",
          "create_dt": "2020-02-06 01:27:19",
          "update_dt": "2020-02-06 01:27:19"
        }
      }
    }
  },
  {
    "_id": 555,
    "name": "demo3",
    "groups": {
      "125": {
        "name": "group3",
        "teams": {
          "310": {
            "name": "team3",
            "team_id": "2322"
          },
          "311": {
            "name": "team3",
            "team_id": "2322"
          }
        }
      }
    }
  }
]

groups hold different key (123,124,125) ? you can share your schema — Mahesh Bhatnagar
– Mahesh Bhatnagar, Commented Feb 6, 2020 at 4:56

Joe · Accepted Answer · 2020-02-06 05:07:06Z

You can get what you're looking from using aggregation.

This aggregation pipeline:

db.collection.aggregate([
  {
    $project: {
      o: {
        $objectToArray: "$groups"
      }
    }
  },
  {
    $match: {
      "o.v.3rd_party_setting.3rd_party_id": "123456"
    }
  },
  {
    $project: {
      key: "$o.v.3rd_party_setting.key"
    }
  }
])

uses $objectToArray on the groups field to split it into k and v in order to examine the contents regardless of the key name
$match on the 3rd_party_id
$project to return just the desired key

If you have any documents with more than 1 group, you might need to $unwind before matching.

Sample playground

Collectives™ on Stack Overflow

how to find records in nested document in mongodb

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related