Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find records with field in a nested document when parent fields are not known

Tags:

mongodb

With a collection with documents like below, I need to find the documents where a particular field - eg. lev3_field2 (in document below) is present.

I tried the following, but this doesn't return any results, though the field lev3_field2 is present in some documents.

db.getCollection('some_collection').find({"lev3_field2": { $exists: true, $ne: null } })

{
    "_id" : ObjectId("5884de15bebf420cf8bb2857"),
    "lev1_field1" : "139521721",
    "lev1_field2" : "276183",
    "lev1_field3" : {
        "lev2_field1" : "4",
        "lev2_field2" : {
            "lev3_field1" : "1",
            "lev3_field2" : {
                "lev4_field1" : "1",
                "lev4_field2" : "1"
            },
            "lev3_field3" : "5"
        },
        "lev2_field3" : {
            "lev3_field3" : "0",
            "lev3_field4" : "0"
        }
    }
}

update1: this is an example, however in the real document it is not known what the parent fields are for the field to look for. So instead of lev3_field2 , I would be looking for `levM_fieldN'.

update2: Speed is not a primary concern for me, I can work with relatively a bit slower options as well, as the primary function is to find documents with the criteria discussed and once the document is found and the schema is understood, the query can be re-written for performance by including the parent keys.

like image 818
user3206440 Avatar asked Feb 04 '17 05:02

user3206440


2 Answers

To search a key in nested document you need to iterate the documents fields recursively, you can do this in JavaScript by the help of $where method in MongoDB The below query will search if a key name exists in a documents and its subdocuments.

I have checked this with the example you have given, and it is working perfectly fine.

db.getCollection('test').find({ $where: function () {
    var search_key = "lev3_field2";

    function check_key(document) {
      return Object.keys(document).some(function(key) {
        if ( typeof(document[key]) == "object" ) {
            if ( key == search_key ) {
                return true;
            } else {
                return check_key(document[key]);
            }
        } else {
          return ( key == search_key );
        }
      });
    }
    return check_key(this);
  }}

);
like image 198
Puneet Singh Avatar answered Oct 23 '22 06:10

Puneet Singh


There is no built-in function to iterate over document keys in MongoDB, but you can achieve this with MapReduce. The main advantage is that all the code is executed directly in the MongoDB database, and not in the js client, so there is no network overhead, hence it should be faster than client side js

here is the script :

    var found;

    // save a function in MongoDB to iterate over documents key and check for 
    // key name. Need to be done only once
    db.system.js.save({
        _id: 'findObjectByLabel',
        value: function(obj, prop) {
            Object.keys(obj).forEach(function(key) {
                if (key === prop) {
                    found = true
                }
                if (!found && typeof obj[key] === 'object') {
                    findObjectByLabel(obj[key], prop)
                }
            })
        }
    })

// run the map reduce fonction 
    db.ex.mapReduce(
        function() {
            found = false;
            var key = this._id
            findObjectByLabel(this, 'lev3_field2')
            value = found;
            if (found) {
              // if the document contains the key we are looking for,
              // emit {_id: ..., value: true }
              emit(key, value)
            }
        },
        function(key, values) {
            return values
        }, {
            'query': {},
            'out': {inline:1}
        }
    )

this output ( run on 4 sample doc, with only one containing 'lev3_field2' )

{
    "results" : [
        {
            "_id" : ObjectId("5884de15bebf420cf8bb2857"),
            "value" : true
        }
    ],
    "timeMillis" : 18,
    "counts" : {
        "input" : 4,
        "emit" : 1,
        "reduce" : 0,
        "output" : 1
    },
    "ok" : 1
}

to run the script, copy it to a file name "script.js" for example, and then run from your shell

mongo databaseName < script.js
like image 3
felix Avatar answered Oct 23 '22 06:10

felix