0

I am new to Mongo DB and NoSQL world. I am trying to find an equivalent of dba_tab_cols of Oracle in Mongo DB. My intention is, if I know a field name, I should be able to find out what all collections have that field by a query. Is it even possible in Mongo DB?

user2974954
  • 169
  • 1
  • 11
  • [Find all collections in mongodb with specific field](https://stackoverflow.com/questions/36491473/find-all-collections-in-mongodb-with-specific-field) – YuTing Dec 24 '21 at 08:11
  • The answer from @prasad_ is fully correct, however MongoDB does not work like you may know from relational databases. When you insert data into a collection which does not exist, then a new collection is created automatically. When you select a collection/field which does not exist then you simply get a `null` value. An error like `Collection does not exist` or `Invalid field name` does not exist in MongoDB. – Wernfried Domscheit Dec 24 '21 at 10:49
  • In case you want to create collections with predefined structure and with constraints / validations on fields - use the optional [Schema Validation](https://docs.mongodb.com/manual/core/schema-validation/). This allows validation during insert and update operations. – prasad_ Dec 27 '21 at 04:17

1 Answers1

0

For example, if you are looking for collections with the field name:

var collections = db.getCollectionNames()
var matches = []
for (let coll of collections) {
    let result = db[coll].findOne({ name: { $exists: true }})
    if (result) matches.push(coll)
}

printjson(matches)

Note that this will include where the field exists and also includes even if the value is null. So, if you want to exclude those collections with field value as null, use the following filter:

let result = db[coll].findOne({ name: { $exists: true, $ne: null }})

For fields which contains a substring of the value you are looking for, use Regex. For example, if you are looking for collections with field names containing name, and this would match for field names "firstName", "lastName" and "name".

let result = db[coll].aggregate([
    { $project: { flds: { "$objectToArray": "$$ROOT" }}}, 
    { $match: { "flds.k": /name/ }},
    { $limit: 1 }
]).hasNext()

NOTE:

  • This runs from mongo shell.
  • These scripts do not look for fields within nested objects and objects within arrays.
  • These look for collections within the current database only. For searching in all databases, you can try:

var dbNames = db.adminCommand({ listDatabases: 1, nameOnly: true })["databases"].map(d => d.name)
for (let dbname of dbNames) {
    let collections = db.getSiblingDB(dbname).getCollectionNames()
    // ...
}
prasad_
  • 12,755
  • 2
  • 24
  • 36