Mongodb - regex match of keys for subdocuments

Question

I have some documents saved in a collection (called urls) that look like this:

{
    payload:{
        url_google.com:{
            url:'google.com',
            text:'search'
        }
    }
},
{
    payload:{
        url_t.co:{
            url:'t.co',
            text:'url shortener'
        }
    }
},
{
    payload:{
        url_facebook.com:{
            url:'facebook.com',
            text:'social network'
        }
    }
}

Using the mongo CLI, is it possible to look for subdocuments of payload that match /^url_/? And, if that's possible, would it also be possible to query on the match's subdocuments (for example, make sure text exists)?

I was thinking something like this:

db.urls.find({"payload":{"$regex":/^url_/}}).count();

But that's returning 0 results.

Any help or suggestions would be great.

Thanks,

Matt

Answer 1

It's not possible to query against document keys in this way. You can search for exact matches using $exists, but you cannot find key names that match a pattern.

I assume (perhaps incorrectly) that you're trying to find documents which have a URL sub-document, and that not all documents will have this? Why not push that type information down a level, something like:

{
  payload: {
    type: "url",
    url: "Facebook.com",
    ...
  }
}

Then you could query like:

db.foo.find({"payload.type": "url", ...})

I would also be remiss if I did not note that you shouldn't use dots (.) is key names in MongoDB. In some cases it's possible to create documents like this, but it will cause great confusions as you attempt to query into embedded documents (where Mongo uses dot as a "path separator" so to speak).

Answer 2

You can do it but you need to use aggregation: Aggregation is pipeline where each stage is applied to each document. You have a wide range of stages to perform various tasks.

I wrote an aggregate pipeline for this specific problem. If you don't need the count but the documents itself you might want to have a look at the $replaceRoot stage.

EDIT: This works only from Mongo v3.4.4 onwards (thanks for the hint @hwase0ng)

db.getCollection('urls').aggregate([
    {
        // creating a nested array with keys and values
        // of the payload subdocument.
        // all other fields of the original document
        // are removed and only the filed arrayofkeyvalue persists
        "$project": {
            "arrayofkeyvalue": {
                "$objectToArray": "$$ROOT.payload"
            }
        }
    },
    {
        "$project": {
            // extract only the keys of the array
            "urlKeys": "$arrayofkeyvalue.k"
        }
    },
    {
        // merge all documents
        "$group": {
            // _id is mandatory and can be set
            // in our case to any value
            "_id": 1,
            // create one big (unfortunately double
            // nested) array with the keys
            "urls": {
                "$push": "$urlKeys"
            }
        }
    },
    {
        // "explode" the array and create
        // one document for each entry
        "$unwind": "$urls"
    },
    {
        // "explode" again as the arry
        // is nested twice ...
        "$unwind": "$urls"
    },
    {
        // now "query" the documents
        // with your regex
        "$match": {
            "urls": {
                "$regex": /url_/
            }
        }
    },
    {
      // finally count the number of
      // matched documents
        "$count": "count"
    }
])