create new variables in aggregation?

[Tim Arnold]

I have a collection with documents like this (one fruit, one format, one result):

{name: 'apple', result: 1, format:'pdf'}

{name: 'apple', result: 0, format:'xml'}

{name: 'apple', result: 1, format:'htm'}

{name: 'pear', result: 0, format:'pdf'}

{name: 'pear', result: 1, format:'xml'}

{name: 'pear', result: 0, format:'htm'}

I want to transform it to these two documents (one fruit, three format variables, three result values):

{name: 'apple', pdf:1, xml:0, htm:1}

{name: 'pear', pdf:0, xml:1, htm:0}

Is this possible?

I am reading the doc on the aggregation pipeline but I'm not sure how/if it can be done.

thanks,

--Tim

[c...@3tsoftwarelabs.com]

Maybe something like this?

db.test.aggregate(
  [
    // Stage 1
    {
      $match: {
        result: 1
      }
    },


    // Stage 2
    {
      $group: {
        _id: "$name",
        formats: { $addToSet: "$format"}
      }
    },


    // Stage 3
    {
      $project: {
        name: "$name",
        pdf: {$setIsSubset: [["pdf"], "$formats"]},
        htm: {$setIsSubset: [["htm"], "$formats"]},
        xml: {$setIsSubset: [["xml"], "$formats"]}
      }
    }
  ]
);

You need the group because your context stretches over multiple documents. The last stage just translates from set representation to your final output format. And the first stage gets rid of all false results, which basically makes group only add true values to the set and as a side-effect also speeds up the group operator itself. I am sure there are other solutions.

Cheers

Chris

[Christoph Husse]

Btw, you can create such aggregation queries pretty easily with MongoChef. Have a look at our tutorial here.

Disclaimer: I am a part of the team of 3T Software Labs.

Cheers

Chris

[Tim Arnold]

Hi Chris,

Thank you very much--that is just what I needed. I will have to do more reading now to make sure I understand what's going on.

MongoChef is very cool too--thanks for the link.

--Tim