Find all documents bases on value in subdocument elements

Working with Data
1

Dear All,
I’m new to MongoDB. I’m trying to built query to access all the customers whose “tier” is “Bronze”. Below is the sample document. Any help is really appreciated.

{
  "_id": {
    "$oid": "5ca4bbcea2dd94ee58162a6d"
  },
  "username": "gregoryharrison",
  "name": "Natalie Ford",
  "address": "17677 Mark Crest\nWalterberg, IA 39017",
  "birthdate": {
    "$date": "1996-09-13T17:14:27.000Z"
  },
  "email": "amyholland@yahoo.com",
  "accounts": [
    904260,
    565468
  ],
  "tier_and_details": {
    "69f8b6a3c39c42edb540499ee2651b75": {
      "tier": "Bronze",
      "benefits": [
        "dedicated account representative",
        "airline lounge access"
      ],
      "active": true,
      "id": "69f8b6a3c39c42edb540499ee2651b75"
    },
    "c85df12c2e394afb82725b16e1cc6789": {
      "tier": "Bronze",
      "benefits": [
        "airline lounge access"
      ],
      "active": true,
      "id": "c85df12c2e394afb82725b16e1cc6789"
    },
    "07d516cfd7fc4ec6acf175bb78cb98a2": {
      "tier": "Gold",
      "benefits": [
        "dedicated account representative"
      ],
      "active": true,
      "id": "07d516cfd7fc4ec6acf175bb78cb98a2"
    }
  }
}
2

Your storage of the tier_and_details seems a little strange, are you using the ID of the relationship between the two as a key?
You may be better having the tier_and_details as an array of items, each of which has the _id field (which you already have anyway).

With the data in this shape it’ll be hard to look for data as the path is different on every document, unless I’m missing something?

{
  "_id": {
    "$oid": "5ca4bbcea2dd94ee58162a6d"
  },
  "username": "gregoryharrison",
  "name": "Natalie Ford",
  "address": "17677 Mark Crest\nWalterberg, IA 39017",
  "birthdate": {
    "$date": "1996-09-13T17:14:27.000Z"
  },
  "email": "amyholland@yahoo.com",
  "accounts": [
    904260,
    565468
  ],
  "tier_and_details": [
    {
      "tier": "Bronze",
      "benefits": [
        "dedicated account representative",
        "airline lounge access"
      ],
      "active": true,
      "id": "69f8b6a3c39c42edb540499ee2651b75"
    },
    {
      "tier": "Bronze",
      "benefits": [
        "airline lounge access"
      ],
      "active": true,
      "id": "c85df12c2e394afb82725b16e1cc6789"
    },
    {
      "tier": "Gold",
      "benefits": [
        "dedicated account representative"
      ],
      "active": true,
      "id": "07d516cfd7fc4ec6acf175bb78cb98a2"
    }
  ]
}

The you could do:

db.getCollection('test').find({'tier_and_details.tier':'Bronze'})

If you wanted to check for people with Bronze that are also active you could then use
$elemMatch.

1 Like
3

Dear John,
Thanks for quick support.
As mentioned earlier I’m new to MongoDB. I got the data in json file and I imported it using import utility without any errors. Hence thought this also a valid document structure.

I’ll try to fix the document structure by putting all the subdocuments as array.

Thanks again.
Best Regards,
FN