Downgrade Mongo 3.6+ query for 3.4

Hi.

I am new to Mongo and have been given the following query to use, but my Mongo DB install is 3.4 and the query uses operators only available in 3.6 and above.

db.getCollection('events').aggregate([
{
    $unwind: "$payload.latest"
},
{
    $unwind: {
        path: "$payload.authorisation.instrument"
    }
},
{
    $lookup: {
        from: "request",
        localField: "payload.uuid",
        foreignField: "uuid",
        as: "request"
    }
},
{
    $unwind: {
        path: "$request"
    }
},
{
    $unwind: {
        path: "$request.responses",
        preserveNullAndEmptyArrays: true
    }
},
{
    $lookup: {
        from: "questionSet",
        localField: "request.questionSet",
        foreignField: "uuid",
        as: "set"
    }
},
{
    $unwind: {
        path: "$set",
        preserveNullAndEmptyArrays: true
    }
},
{
    $unwind: {
        path: "$set.questions",
        preserveNullAndEmptyArrays: true
    }
 },
 {
    $unwind: {
        path: "$set.questions.responses",
        preserveNullAndEmptyArrays: true
    }
 },
 {
    $match: {
        $expr: {
            $and: [
                { $eq: [ "$request.responses.questionId", "$set.questions.id" ] },
                { $eq: [ "$request.responses.responseId", "$set.questions.responses.id" ] }
            ]
        }
    }
},
{
    $project: {
        instr: "$payload.authorisation.instrument",
        subjects: "$request.subjects",
        purchaser: "$request.createdBy",
        orderReference: "$request.submissionReference",
        businessUnit: "$createdByBusinessUnit",
        serviceCode: "$payload.latest.targeting.serviceCode",
        bizCaseField1: "$request.bizCaseField1",
        bizCaseField2: "$request.bizCaseField2",
        bizCaseField3: "$request.bizCaseField3",
        questions: {
            questionText: "$set.questions.text",
            responseText: "$set.questions.responses.name"
        }
    }
},
{   $group: {
        _id: { id: "$_id", instr: "$instr", product: "$product" },
        root: { $mergeObjects: '$$ROOT' },
        questions: { $push: "$questions" }
    }
},
{
    $replaceRoot: {
        newRoot: {
            $mergeObjects: ['$root', '$$ROOT']
        }
    }
},
{
    $project: {
        _id: 0,
        root: 0
    }
},
{
    $sort: {
        instr: 1,
        subjects: 1
    }
}]).pretty();

I think it is the $expr and $mergeObjects operators that aren’t supported.

I’m not sure what to replace these with.

Any ideas please?

Hello, @Sean_Barry!

  1. $expr operator inside $match stage can be replaced with intermediate matching field.
    The idea is to use matching conditions from $expr operator calculate an intermediate field within $addFields stage. Later, using $match stage, filter the documents by that field. Intermediate field can be removed after it was used with the $project stage.

Example dataset:

db.figures.insertMany([
  { _id: 'A', width: '15cm', height: '15cm' },
  { _id: 'B', width: '15cm', height: '10cm' }
]);

Example of aggregation pipeline with the $expr opetator:

db.figures.aggregate([
  {
    $match: {
      $expr: {
        $eq: ['$width', '$height'],
      }
    }
  }
]);

Example of aggregation pipeline with the intermediate matching field:

db.figures.aggregate([
  {
    $addFields: {
      // calculate intermediate prop
      isSquare: {
        $cond: {
          if: {
            // put here all the conditions,
            // that your have inside $expr
            $eq: ['$width', '$height'],
          },
          then: true,
          else: false,
        }
      }
    }
  },
  {
    $match: {
      // match by that intermediate prop
      isSquare: true
    }
  },
  {
    $project: {
      // remove intermediate prop from ouput
      isSquare: false,
    }
  }
]);

Output of both aggregations is the same:

{ "_id" : "A", "width" : "15cm", "height" : "15cm" }
  1. There are two ways of replacing $mergeObjects:
    • by converting merged objects to arrays, concatenate them and convert back to object;
    • by specifying all the possible properties that can be in the both objects and determine conditions, that would resolve missing values;

Example dataset:

db.learningPlans.insertOne({
  initialPlan: {
    learnJavascript: true,
    learnDesignPatterns: true,
    learnAgile: true,
  },
  currentPlan: {
    learnMongoDB: true,
    learnAgile: false,
  }
});

Example of aggregation pipeline with $mergeObjects operator:

db.learningPlans.aggregate([
  {
    $project: {
      latestPlan: {
        $mergeObjects: ['$initialPlan', '$currentPlan']
      }
    }
  }
]);

Example of aggregation pipeline with objects-array-object manipulations.
Important: $objectToArray and $arrayToObject were added in MongoDB v3.4.4.

db.learningPlans.aggregate([
  {
    $addFields: {
      // disassemble arrays for further manipulations
      intermediateBase: {
        $objectToArray: '$initialPlan',
      },
      intermediateOverwrite: {
        $objectToArray: '$currentPlan',
      }
    },
  },
  {
    // add another $addFields stages,
    // so props from previous $addFields
    // will be accessible here
    $addFields: {
      intermediateFinal: {
        // order of arguments must be the same, that 
        // was used in $mergeObjects operator
        $concatArrays: ['$intermediateBase', '$intermediateOverwrite']
      }
    }
  },
  {
    $project: {
      latestPlan: {
        $arrayToObject: ['$intermediateFinal']
      }
    }
  }
]).pretty();

Example of aggregation pipeline with prop list and $cond operator.
Notice, that you need to list all the props, that appear in both objects, that you need to merge and for each property you need to add logic to handle missing properties.

db.learningPlans.aggregate([
  {
    $project: {
      latestPlan: {
        learnJavascript: {
          $cond: {
            if: {
              $eq: ['$currentPlan.learnJavascript', undefined],
            },
            then: '$initialPlan.learnJavascript',
            else: '$currentPlan.learnJavascript'
          }
        },
        learnDesignPatterns: {
          $cond: {
            if: {
              $eq: ['$currentPlan.learnDesignPatterns', undefined],
            },
            then: '$initialPlan.learnDesignPatterns',
            else: '$currentPlan.learnDesignPatterns'
          }
        },
        learnAgile: {
          $cond: {
            if: {
              $eq: ['$currentPlan.learnAgile', undefined],
            },
            then: '$initialPlan.learnAgile',
            else: '$currentPlan.learnAgile'
          }
        },
        learnMongoDB: {
          $cond: {
            if: {
              $eq: ['$currentPlan.learnMongoDB', undefined],
            },
            then: '$initialPlan.learnMongoDB',
            else: '$currentPlan.learnMongoDB'
          }
        }
      }
    }
  }
]).pretty();

Output of the above 3 aggregations is the same:

[
  {
    "_id" : ObjectId("5f18afb74d4bfee817d2e4e2"),
    "latestPlan" : {
      "learnJavascript" : true,
      "learnDesignPatterns" : true,
      "learnAgile" : false,
      "learnMongoDB" : true
    }
  }
]