I'm tring to map some results from mongo db into a table for generate an Excel report. My Java code generates the report from a List<Map<String, Object>> where Object can be number, string date or boolean.

On Mongo I was able to obtain the results as I need with this command:

db.collection.find(query,projection).map(function).flat();

But when I tried to replicate this command on Java I found that the eval comand was deprecated and that is not possible to execute javascript from Java Driver. I would like to execute the mapping on database instead of mapping in my JAVA class. And I would like to execute an "native query" instead of using Spring Data to better manipulate my report query if needed.

I'm new to mongo and searching for a solution I found the aggregation pipeline, but I'm not sure that can help me.

Is it possible to obtain the same results with an aggregation pipeline or an other commad that can be executed with JAVA driver?

This is my query

db.data_export.find({
$and: [
  { "data": {$exists: true}},
  {$or: [
    {"data.code1": {$exists: true}},
    {"data.code2": {$exists: true}},
    {"data.code3": {$exists: true}}
    ]
}, {
    companyId: 1,
    companyName: 1,
    "data.code1.iterations": 1,
    "data.code2.iterations":1,
    "data.code3.iterations":1
}).map(function(v) {
    let maxSize = 0;
    let codes = ['code1',
        'code2',
        'code3'];
    let codeFounded = [];
    for(const [key, value] of Object.entries(v.data)) {
        if(value && value.iterations){
            maxSize = Math.max(value.iterations.length, maxSize);
            codeFounded.push(key);
        }
    }
    let res = []
    for(let i=0; i<maxSize;i  ){
        let o = {
            companyId: v.companyId,
            companyName: v.companyName
        }
        for(let s=0; s< codes.length; s  ){
            const code = codes[s];
            let val = null;
            if(codeFounded.includes(code)){
                val = v.data[code].iterations[i];
            }
            o[code] = val;
        }
        res.push(o);
    }
    return res;
}).flat();

This is my model

{
    "companyId": 1,
    "companyName": "Company Name 1",
    "data": {
        "code1": {
            "iterations": [
                {
                    "procedure": "Functions"
                },
                {
                    "procedure": "Planning"
                },
                {
                    "procedure": "Monitoring"
                }
            ]
        },
    "code2": {
        "iterations": [
                {
                    "digitization": true
                }
            ]
        },
    "code3": {
        "iterations": [
                {
                    "meeting_freq": "Every month"                       
                }
            ]
        }
    }
}

This is what I'm obtain as result like the follow table:

CodePudding user response：

This was definitely slightly tricky, but you're right it is achievable in a single native query, here's how to do it:

db.collection.aggregate([
  {
    $match: {
      $or: [
        {
          "data.code1": {
            $exists: true
          }
        },
        {
          "data.code2": {
            $exists: true
          }
        },
        {
          "data.code3": {
            $exists: true
          }
        }
      ]
    }
  },
  {
    $addFields: {
      maxSize: {
        $reduce: {
          input: {
            "$objectToArray": "$data"
          },
          initialValue: 0,
          in: {
            $max: [
              "$$value",
              {
                $size: "$$this.v.iterations"
              }
            ]
          }
        }
      }
    }
  },
  {
    $addFields: {
      values: {
        $map: {
          input: {
            $range: [
              0,
              "$maxSize"
            ]
          },
          as: "iter",
          in: {
            $arrayToObject: {
              $map: {
                input: {
                  "$objectToArray": "$data"
                },
                as: "datum",
                in: {
                  k: "$$datum.k",
                  v: {
                    $ifNull: [
                      {
                        $arrayElemAt: [
                          "$$datum.v.iterations",
                          "$$iter"
                        ]
                      },
                      null
                    ]
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  {
    $unwind: "$values"
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          {
            companyId: "$companyId",
            companyName: "$companyName",
            
          },
          "$values"
        ]
      }
    }
  }
])

Mongo Playground