When I first started playing with MongoDB aggregation framework queries, I quickly realized that '$project' stage could be hugely useful to completely reshape documents in cases where you structured them one way, but you need to get the results shaped differently.
I thought it would be particularly helpful if you could map keys (aka field names) to be field values and vice versa. This feature did not exist (and still doesn't) so I filed a request for it in MongoDB issue tracking system.
Meanwhile, as I show here it is still possible to do this "projection" via aggregation if you know the names of the fields in advance.
But what if you get the names of fields dynamically, rather than always knowing they will be field1, field2?
I'm going to show how you can generate the appropriate aggregation framework pipeline programmatically based on the set of field names passed in. I'm going to use Javascript in the shell as the most general example, but you can translate this into your language of choice and adjust accordingly.
My sample documents and schema will be like this:
I thought it would be particularly helpful if you could map keys (aka field names) to be field values and vice versa. This feature did not exist (and still doesn't) so I filed a request for it in MongoDB issue tracking system.
Meanwhile, as I show here it is still possible to do this "projection" via aggregation if you know the names of the fields in advance.
But what if you get the names of fields dynamically, rather than always knowing they will be field1, field2?
I'm going to show how you can generate the appropriate aggregation framework pipeline programmatically based on the set of field names passed in. I'm going to use Javascript in the shell as the most general example, but you can translate this into your language of choice and adjust accordingly.
My sample documents and schema will be like this:
{
"_id" : 1,
"attr" : [
{ "k": "firstName",
"v": "Asya" },
{ "k": "lastName",
"v": "Kamsky" },
{ "k": "employer",
"v": "10gen, the MongoDB company" },
{ "k": "URL",
"v": "http://www.kamsky.org" }
]
} This is a pretty standard way of storing dynamic attributes - properties of the document that can't be easily enumerated in advance either because they are not all known, because they can vary widely for different types of documents or both. It makes it possible to index the attributes by creating a compound index on {"attr.k":1,"attr.v":1} fields and now querying on something like {"attr.k":"color","attr.v":"blue"} will use the index. An alternative way of storing keys as field names and values as field values has other advantages but it makes it difficult to have a good indexing strategy as you may end up with a large number of indexes and every time you add a new type of attribute you have to create a new index to support it. Sparse indexes can help, but they can create their own challenges worthy of a separate blog post.
Now imagine I want to output documents which have this shape:
Now imagine I want to output documents which have this shape:
{
"_id" : 1,
"firstName" : "Asya",
"lastName" : "Kamsky",
"employer" : "10gen, the MongoDB company"
} Given I will be passed an array called 'fields' which contains values 'firstName', 'lastName' and 'employer' here is how I will build the pipeline stages for my aggregation.
/* my array of wanted fields */
fields = [ "firstName", "lastName", "employer"];
/* first I unwind the attributes array in each document */
unwind = {"$unwind" : "$attr"};
/* I only keep the attributes I want to return */
match = { "$match" : { "attr.k" : { "$in" : fields } } };
/* I create new fields by setting correct value if key *
* matches, or some known value I can "skip" later */
project = { "$project" : { } } ;
fields.forEach( function(f) {
project["$project"][f] = { "$cond" :
[ { "$eq" : [ f, "$attr.k" ] },
"$attr.v", " skip"
] };
} );
/* I regroup the original document using $max to *
* trick it into keeping only non-skip value */
group = { "$group" : { "_id" : "$_id" } } ;
fields.forEach( function(f) {
group["$group"][f] = { "$max" : "$" + f };
} );
/* now run the aggregation */
db.collection.aggregate( unwind, match, project, group );
{
"result" : [
{
"_id" : 1,
"firstName" : "Asya",
"lastName" : "Kamsky",
"employer" : "10gen, the MongoDB company"
}
],
"ok" : 1
}
I did make some assumptions about the data:
I hope this is helpful, if not for actual implementation, at least for thinking about how you want to structure your documents. In the future I will take a closer look at advantages and disadvantages of different ways of storing attributes in your schema design.
- no duplicate attribute names for a particular document
- all attribute values would be alphanumeric and compare greater than "space" character (ASCII x20)
I hope this is helpful, if not for actual implementation, at least for thinking about how you want to structure your documents. In the future I will take a closer look at advantages and disadvantages of different ways of storing attributes in your schema design.
RSS Feed
