Query and filter key names instead of values in Mo

Question

I want to find all key names from a collection that partially match a certain string.

The closest I got was to check if a certain key exists, but that's an exact match:

db.collection.find({ "fkClientID": { $exists:1 }})

I'd like to get all keys that start with fk instead.

Answer 1

You can do that using mapReduce:

To get just the field names at root level:

db.collection.mapReduce(function () {
    Object.keys(this).map(function(key) {
        if (key.match(/^fk/)) emit(key, null);

        // OR: key.indexOf("fk") === 0
    });
}, function(/* key, values */) {
    // No need for params or to return anything in the 
    // reduce, just pass an empty function.
}, { out: { inline: 1 }});

This will output something like this:

{
    "results": [{
        "_id": "fkKey1",
        "value": null
    }, {
        "_id": "fkKey2",
        "value": null
    }, {
        "_id": "fkKey3",
        "value": null
    }],
    "timeMillis": W,
    "counts": {
        "input": X,
        "emit": Y,
        "reduce": Z,
        "output": 3
    },
    "ok" : 1
}

To get field names and any or all (whole doc) its values:

db.test.mapReduce(function () {
    var obj = this;

    Object.keys(this).map(function(key) {
        // With `obj[key]` you will get the value of the field as well.
        // You can change `obj[key]` for:
        //  - `obj` to return the whole document.
        //  - `obj._id` (or any other field) to return its value.

        if (key.match(/^fk/)) emit(key, obj[key]);
    });
}, function(key, values) {
    // We can't return values or an array directly yet:

    return { values: values };
}, { out: { inline: 1 }});

This will output something like this:

{
    "results": [{
        "_id": "fkKey1",
        "value": {
            "values": [1, 4, 6]
        }
    }, {
        "_id": "fkKey2",
        "value": {
            "values": ["foo", "bar"]
        }
    }],
    "timeMillis": W,
    "counts": {
        "input": X,
        "emit": Y,
        "reduce": Z,
        "output": 2
    },
    "ok" : 1
}

To get field names in subdocuments (without path):

To do that you will have to use store JavaScript functions on the Server:

db.system.js.save({ _id: "hasChildren", value: function(obj) {
    return typeof obj === "object";
}});

db.system.js.save({ _id: "getFields", value: function(doc) {
    Object.keys(doc).map(function(key) {
        if (key.match(/^fk/)) emit(key, null);

        if (hasChildren(doc[key])) getFields(doc[key])
    });
}});

And change your map to:

function () {
    getFields(this);
}

Now run db.loadServerScripts() to load them.

To get field names in subdocuments (with path):

The previous version will just return field names, not the whole path to get them, which you will need if what you want to do is rename those keys. To get the path:

db.system.js.save({ _id: "getFields", value: function(doc, prefix) {
    Object.keys(doc).map(function(key) {
        if (key.match(/^fk/)) emit(prefix + key, null);

        if (hasChildren(doc[key]))
            getFields(doc[key], prefix + key + '.')
    });
}});

And change your map to:

function () {
    getFields(this, '');
}

To exclude overlapping path matches:

Note that if you have a field fkfoo.fkbar, it will return fkfoo and fkfoo.fkbar. If you don't want overlapping path matches, then:

db.system.js.save({ _id: "getFields", value: function(doc, prefix) {
    Object.keys(doc).map(function(key) {
        if (hasChildren(doc[key]))
            getFields(doc[key], prefix + key + '.')
        else if (key.match(/^fk/)) emit(prefix + key, null);
    });
}});

Going back to your question, renaming those fields:

With this last option, you get all the paths that include keys that start with fk, so you can use $rename for that.

However, $rename doesn't work for those that contain arrays, so for those you could use forEach to do the update. See MongoDB rename database field within array

Performance note:

MapReduce is not particularly fast thought, so you may want to specify { out: "fk_fields"} to output the results into a new collection called fk_fields and query those results later, but that will depend on your use case.

Possible optimisations for specific cases (consistent schema):

Also, note that if you know that the schema of your documents is always the same, then you just need to check one of them to get its fields, so you can do that adding limit: 1 to the options object or just retrieving one document with findOne and reading its fields in the application level.

Answer 2

If you have the latest MongoDB 3.4.4 then you can use $objectToArray in an aggregate statement with $redact as the the most blazing fast way this can possibly be done with native operators. Not that scanning the collection is "fast". but as fast as you get for this:

db[collname].aggregate([
  { "$redact": {
    "$cond": {
      "if": {
        "$gt": [
          { "$size": { "$filter": {
            "input": { "$objectToArray": "$$ROOT" },
            "as": "doc",
            "cond": {
              "$eq": [ { "$substr": [ "$$doc.k", 0, 2 ] }, "fk" ]
            }
          }}},
          0
        ]
      },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }}
 ])

The presently undocumented $objectToArray translates an "object" into "key" and "value" form in an array. So this:

{ "a": 1, "b": 2 }

Becomes this:

[{ "k": "a", "v": 1 }, { "k": "b", "v": 2 }]

Used with $$ROOT which is a special variable referring to the current document "object", we translate to an array so the values of "k" can be inspected.

Then it's just a matter of applying $filter and using $substr to get the preceding characters of the "key" string.

---

For the record, this would be the MongoDB 3.4.4 optimal way of obtaining an unique list of the matching keys:

db[collname].aggregate([
  { "$redact": {
    "$cond": {
      "if": {
        "$gt": [
          { "$size": { "$filter": {
            "input": { "$objectToArray": "$$ROOT" },
            "as": "doc",
            "cond": {
              "$eq": [ { "$substr": [ "$$doc.k", 0, 2 ] }, "fk" ]
            }
          }}},
          0
        ]
      },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }},
  { "$project": { 
    "j": {
      "$filter": {
        "input": { "$objectToArray": "$$ROOT" },
         "as": "doc",
         "cond": {
           "$eq": [ { "$substr": [ "$$doc.k", 0, 2 ] }, "fk" ]
         }
      }
    }
  }},
  { "$unwind": "$j" },
  { "$group": { "_id": "$j.k" }}
 ])

That's the safe provision, which is considering that the key may not be present in all documents and that there could possibly be multiple keys in the document.

If you are absolutely certain that you "always" have the key present in the document and that there will only be one, then you can shorten to just $group:

db[colname].aggregate([
  { "$group": { 
    "_id": {
      "$arrayElemAt": [
        { "$map": {
          "input": { "$filter": {
            "input": { "$objectToArray": "$$ROOT" },
             "as": "doc",
             "cond": {
               "$eq": [ { "$substr": [ "$$doc.k", 0, 2 ] }, "fk" ]
             }
          }},
          "as": "el",
          "in": "$$el.k"
        }},
        0
      ]
    }
  }}
 ])

---

The most efficient way in earlier versions would be using the $where syntax that allows a JavaScript expression to evaluate. Not that anything that evaluates JavaScript is the "most" efficient thing you can do, but analyzing "keys" as opposed to "data" is not optimal for any data store:

db[collname].find(function() { return Object.keys(this).some( k => /^fk/.test(k) ) })

The inline function there is just shell shorthand and this could also be written as:

db[collname].find({ "$where": "return Object.keys(this).some( k => /^fk/.test(k) )" })

The only requirement for $where is that the expression returns a true value for any document you want to return, so the documents return unaltered.