Question 115523 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – Mongoose aggregate: how to create fields dynamically from the user request

Please someone help me! I can’t find the solution in documentation or other topics.

I’m using mongodb aggregation in Mongoose/Nest.js project to return the document data with some formatting and filtering. I have the structure of the mongo document like

{
_id: '1',
outputs: [
  {
  fileName: 'fileName1',
  data: [
   {
     columnName1: 3,
     columnName2: 4,
     ........
     columnName30: 5
   },
   {
     columnName1: 1,
     columnName2: 2,
     ........
     columnName30: 3
   },
   ...........
  ]

 },
 {
  fileName: 'fileName1',
  data: [
   {
     columnName1: 3,
     columnName2: 4,
     ........
     columnName30: 5
   },
   {
     columnName1: 1,
     columnName2: 2,
     ........
     columnName30: 3
   },
   ...........
  ]
 }
........
]
}

I’ve already done some formatting, but now I need to include to the response only requested by the user fields (columnNamesToChoose). And filter their values depending on gte, lte of mainColumnName. Inside $project I was going to use some mapping like this, but it doesn’t work. Could you please help me to fix this part of code?

...columnNamesToChoose.map((columnName) => ({ [columnName]: {
 $map: {
              input: {
                $filter: {
                  input: '$outputs.data',
                  as: 'item',
                  cond: {
                    $and: [
                      { $gte: [`$$item.${mainColumnName}`, gte] },
                      { $lte: [`$$item.${mainColumnName}`, lte] },
                    ],
                  },
                },
              },
              as: 'file',
              in: `$$file.${columnName}`,
            },

} })),

This is the full code of aggregation:

mainColumnName = 'column1' (from the body of the user request)
columnNamesToChoose = ['column2', 'column5'] (from the body of the user request)
myModel.aggregate([
      {
        $match: { _id: Number(id) },
      },
      { $unwind: '$outputs' },
      {
        $match: { 'outputs.fileName': fileName },
      },
      {
        $project: {
          _id: '$_id',
          fileName: '$outputs.fileName',
          [mainColumnName]: {
            $map: {
              input: {
                $filter: {
                  input: '$outputs.data',
                  as: 'item',
                  cond: {
                    $and: [
                      { $gte: [`$$item.${mainColumnName}`, gte] },
                      { $lte: [`$$item.${mainColumnName}`, lte] },
                    ],
                  },
                },
              },
              as: 'file',
              in: `$$file.${mainColumnName}`,
            },
          },
        },
      },
    ])

My result:

{
 "0": {
        "column2": [
            4,
            2,
            1,
            5
        ]
    },
    "1": {
        "column5": [
            1,
            8,
            9,
            0
        ]
    },
    "_id": 1,
    "fileName": "somefilename.txt",
    "column1": [
        3,
        1,
        2,
        20
    ],
}

Expected result:

{
"_id": 1,
        "fileName": "somefilename.txt",
        "column1": [
            3,
            1,
            2,
            20
        ],
        "column2": [
            4,
            2,
            1,
            5
        ],
        "column5": [
            1,
            8,
            9,
            0
        ],
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I've found the most readable and useful answer for me, it's to use $function inside $project and get all the data I need in args using only js and no mongo syntax


  2. 0

    One option is to first $reduce and then $unwind, $match and $group, where the $group stage is built dynamically on the code (for-loop) according to the input:

    db.collection.aggregate([
  {$match: {_id: id}},
  {$project: {
      outputs: {
        $reduce: {
          input: "$outputs",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              {$cond: [
                  {$eq: ["$$this.fileName", fileName]},
                  "$$this.data",
                  []
                ]
              }
            ]
          }
        }
      }
    }
  },
  {$unwind: "$outputs"},
  {$match: {"outputs.columnName1": {$gte: gte, $lte: lte}}},
  {$group: {
      _id: 0,
      column1: {$push: "$outputs.columnName1"},
      column2: {$push: "$outputs.columnName2"},
      column5: {$push: "$outputs.columnName5"}
  }},
  {$set: {fileName: fileName}}
])

    See how it works on the playground example

    On js it will look something like:

    const matchStage = {$match: {}};
matchStage.$match[`outputs.${mainColumnName}`] = {$gte: gte, $lte: lte};
const groupStage = {$group: {_id: 0}};
for (const col of columnNamesToChoose ) {
  groupStage.$group[col] = {$push: `"$outputs.${col}"`}
};
const aggregation = [
  {$match: {_id: id}},
  {$project: {
      outputs: {$reduce: {
          input: "$outputs",
          initialValue: [],
          in: {$concatArrays: [
              "$$value",
              {$cond: [
                  {$eq: ["$$this.fileName", fileName]},
                  "$$this.data",
                  []
              ]}
          ]}
      }}
  }},
  {$unwind: "$outputs"},
  matchStage,
  groupStage,
  {$set: {fileName: fileName}}
],
  
const res = await myModel.aggregate(aggregation)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search