Here is my schema,

    {
  "_id": {
    "$oid": "62dfef1a6fa3ca52622b02e4"
  },
  "dealer_id": 1499,
  "credentials": {
    "62e12c210b2d6": {
      "account_id": "942646",
      "terminal_id": "310012",
      "provider": "Epay",
      "supplier": "verizon",
      "username": "hello",
      "password": "world",
      "portal_link": "https://localhost",
      "certificate_key": "olsjhflsaknfdlaksfd",
      "certificate_file": "aa.txt"
    }
  },
  "updated_at": {
    "$date": {
      "$numberLong": "1659553693372"
    }
  },
  "created_at": {
    "$date": {
      "$numberLong": "1658842906093"
    }
  }
}

Each credentials will have dynamic uniqid, how can i query by username? there will be multiple credentials for each document and how can i search by any field i also need to retrive only dealer_id which is in parent.

Thank you

CodePudding user response：

I recommend you rethink your schema as this is very hard and inefficient to work with, for instance this query cannot be supported by an index and will always result in a collection scan (unless you want to add a wildcard text index but that has it's own complications)

To query it you'll have to convert the object to an array and restructure it, like so:

db.collection.find({
  $expr: {
    $gt: [
      {
        $size: {
          $filter: {
            input: {
              $objectToArray: "$credentials"
            },
            cond: {
              $in: [
                "hello",
                {
                  $map: {
                    input: {
                      $objectToArray: "$$this"
                    },
                    in: "$$this.v.username"
                  }
                },
                
              ]
            }
          }
        }
      },
      0
    ]
  }
})

Mongo Playground