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

Mongodb – How can i aggregate filter nested documents and get value from other field

I have a collection like this:

{
  '_id' : ObjectId('6251f8556e75125f9260f333'),
  'name': 'jojo',
  'profile': 'jojo profile',
  'date': ISODate("2022-04-09T21:18:40.473Z"),
  'look': [
    { 'art': 'group-id', 'data': 'alma', 'dt': '1'},
    { 'art': 'called', 'data': 'central', 'dt': '1'},
    { 'art': 'access-time', 'data': 108000, 'dt': '1'}
  ]
  'answer': [
    { 'art': 'rate-id', 'data': 'limit1', 'dt': '1'},
    { 'art': 'protocol', 'data': 'tcp', 'dt': '1'}
  ]
},
{
  '_id' : ObjectId('6251f8306e75125f9260f332'),
  'name': 'dodo',
  'profile': 'dodo profile',
  'date': ISODate("2022-04-09T15:20:58.562Z"),
  'look': [
    { 'art': 'group-id', 'data': 'alma', 'dt': '1'},
    { 'art': 'called', 'data': 'central', 'dt': '1'},
  ]
  'answer': [
    { 'art': 'rate-id', 'data': 'limit1', 'dt': '1'},
  ]
},
{
  '_id' : ObjectId('6251a5113700ba4a0a59c48f'),
  'name': 'kaka',
  'profile': 'kaka profile',
  'date': ISODate("2022-04-09T15:22:25.816Z"),
  'look': [
    { 'art': 'access-time', 'data': 50400, 'dt': '1'}
  ]
  'answer': [
    { 'art': 'protocol', 'data': 'tcp', 'dt': '1'}
  ]
}

and I was expecting an output like this:

{
  '_id' : ObjectId('6251f8556e75125f9260f333'),
  'name': 'jojo',
  'profile': 'jojo profile',
  'date': ISODate("2022-04-09T21:18:40.473Z"),
  'goup': 'alma',        // filter by 'group-id' and put value of data field
  'called': 'central',   // filter by 'called' and put value of data field
  'accessTime': 108000,  // filter by 'access-time' and put value of data field
  'rate': 'limi1',       // filter by 'rate-id' and put value of data field
  'protocol': 'tcp',     // filter by 'protocol' and put value of data field
},
{
  '_id' : ObjectId('6251f8306e75125f9260f332'),
  'name': 'dodo',
  'profile': 'dodo profile',
  'date': ISODate("2022-04-09T15:20:58.562Z"),
  'goup': 'alma',
  'called': 'central',
  'accessTime': '',     // set blank data if not exist
  'rate': 'limi1',
  'protocol': '',       // set blank data if not exist
},
{
  '_id' : ObjectId('6251a5113700ba4a0a59c48f'),
  'name': 'kaka',
  'profile': 'kaka profile',
  'date': ISODate("2022-04-09T15:22:25.816Z"),
  'goup': '',          // set blank data if not exist
  'called': '',        // set blank data if not exist
  'accessTime': 50400,
  'rate': '',          // set blank data if not exist
  'protocol': 'tcp',
}

I’ve searched here but couldn’t find an answer that matches the problem I’m facing, probably because of the wrong keywords.
Since I’m new to mongodb, I’m confused about how to solve the query I want. How can I achieve this? Please help me…

Tags:

3

Answers


  1. 0

    For this you should use the aggregation framework of mongo db, because will require complex operations to get the data in the shape that you want.

    https://www.mongodb.com/docs/manual/aggregation/

    Every aggregation is an array of stages and every stage does something specific.

    I used the next stages:

    1. addFields: Allows you to add new fields to the response of every document, so if you don’t have group in the document, that will add or replace it.
    2. project: Allows you remove some fields of a document. In a projection stage if you set an attribute as 0 that will remove that attribute from the response.

    Also I used some operators:

    1. filter: this allows you to filter data of an element that is an array
    2. arrayElemenAt: receives an array and return the position specified

    The pipeline:

    [
   {
      "$addFields":{
         "group":{
            "$arrayElemAt":[
               {
                  "$filter":{
                     "input":"$look",
                     "as":"item",
                     "cond":{
                        "$eq":[
                           "$$item.art",
                           "group-id"
                        ]
                     }
                  }
               },
               0
            ]
         },
         "called":{
            "$arrayElemAt":[
               {
                  "$filter":{
                     "input":"$look",
                     "as":"item",
                     "cond":{
                        "$eq":[
                           "$$item.art",
                           "called"
                        ]
                     }
                  }
               },
               0
            ]
         },
         "accessTime":{
            "$arrayElemAt":[
               {
                  "$filter":{
                     "input":"$look",
                     "as":"item",
                     "cond":{
                        "$eq":[
                           "$$item.art",
                           "access-time"
                        ]
                     }
                  }
               },
               0
            ]
         },
         "rate":{
            "$arrayElemAt":[
               {
                  "$filter":{
                     "input":"$answer",
                     "as":"item",
                     "cond":{
                        "$eq":[
                           "$$item.art",
                           "rate-id"
                        ]
                     }
                  }
               },
               0
            ]
         },
         "protocol":{
            "$arrayElemAt":[
               {
                  "$filter":{
                     "input":"$answer",
                     "as":"item",
                     "cond":{
                        "$eq":[
                           "$$item.art",
                           "protocol"
                        ]
                     }
                  }
               },
               0
            ]
         }
      }
   },
   {
      "$addFields":{
         "group":"$group.data",
         "called":"$called.data",
         "accessTime":"$accessTime.data",
         "rate":"$rate.data",
         "protocol":"$protocol.data"
      }
   },
   {
      "$project":{
         "look":0,
         "answer":0
      }
   }
]
    Login or Signup to reply.
  2. 0

    This is quite cumbersome with the current structure, as for each field you have to convert the object to an array, filter it then convert it back, here’s how it looks:

    db.collection.aggregate([
  {
    $replaceRoot: {
      newRoot: {
        "$mergeObjects": [
          {
            _id: "$_id",
            name: "$name",
            profile: "$profile",
            date: "$date",
            
          },
          {
            "$arrayToObject": {
              $map: {
                input: {
                  $filter: {
                    input: {
                      $objectToArray: {
                        $ifNull: [
                          {
                            "$arrayElemAt": [
                              {
                                $filter: {
                                  input: {
                                    $ifNull: [
                                      "$look",
                                      []
                                    ]
                                  },
                                  cond: {
                                    $eq: [
                                      "$$this.art",
                                      "group-id"
                                    ]
                                  }
                                }
                              },
                              0
                            ]
                          },
                          {
                            art: ""
                          }
                        ]
                      }
                    },
                    cond: {
                      $eq: [
                        "$$this.k",
                        "data"
                      ]
                    }
                  }
                },
                in: {
                  k: "goup",
                  v: "$$this.v"
                }
              }
            }
          },
          {
            "$arrayToObject": {
              $map: {
                input: {
                  $filter: {
                    input: {
                      $objectToArray: {
                        $ifNull: [
                          {
                            "$arrayElemAt": [
                              {
                                $filter: {
                                  input: {
                                    $ifNull: [
                                      "$look",
                                      []
                                    ]
                                  },
                                  cond: {
                                    $eq: [
                                      "$$this.art",
                                      "called"
                                    ]
                                  }
                                }
                              },
                              0
                            ]
                          },
                          {
                            art: ""
                          }
                        ]
                      }
                    },
                    cond: {
                      $eq: [
                        "$$this.k",
                        "data"
                      ]
                    }
                  }
                },
                in: {
                  k: "called",
                  v: "$$this.v"
                }
              }
            }
          },
          {
            "$arrayToObject": {
              $map: {
                input: {
                  $filter: {
                    input: {
                      $objectToArray: {
                        $ifNull: [
                          {
                            "$arrayElemAt": [
                              {
                                $filter: {
                                  input: {
                                    $ifNull: [
                                      "$look",
                                      []
                                    ]
                                  },
                                  cond: {
                                    $eq: [
                                      "$$this.art",
                                      "access-time"
                                    ]
                                  }
                                }
                              },
                              0
                            ]
                          },
                          {
                            art: ""
                          }
                        ]
                      }
                    },
                    cond: {
                      $eq: [
                        "$$this.k",
                        "data"
                      ]
                    }
                  }
                },
                in: {
                  k: "access-time",
                  v: "$$this.v"
                }
              }
            }
          },
          {
            "$arrayToObject": {
              $map: {
                input: {
                  $filter: {
                    input: {
                      $objectToArray: {
                        $ifNull: [
                          {
                            "$arrayElemAt": [
                              {
                                $filter: {
                                  input: {
                                    $ifNull: [
                                      "$answer",
                                      []
                                    ]
                                  },
                                  cond: {
                                    $eq: [
                                      "$$this.art",
                                      "rate-id"
                                    ]
                                  }
                                }
                              },
                              0
                            ]
                          },
                          {
                            art: ""
                          }
                        ]
                      }
                    },
                    cond: {
                      $eq: [
                        "$$this.k",
                        "data"
                      ]
                    }
                  }
                },
                in: {
                  k: "rate",
                  v: "$$this.v"
                }
              }
            }
          },
          {
            "$arrayToObject": {
              $map: {
                input: {
                  $filter: {
                    input: {
                      $objectToArray: {
                        $ifNull: [
                          {
                            "$arrayElemAt": [
                              {
                                $filter: {
                                  input: {
                                    $ifNull: [
                                      "$answer",
                                      []
                                    ]
                                  },
                                  cond: {
                                    $eq: [
                                      "$$this.art",
                                      "protocol"
                                    ]
                                  }
                                }
                              },
                              0
                            ]
                          },
                          {
                            art: ""
                          }
                        ]
                      }
                    },
                    cond: {
                      $eq: [
                        "$$this.k",
                        "data"
                      ]
                    }
                  }
                },
                in: {
                  k: "protocol",
                  v: "$$this.v"
                }
              }
            }
          }
        ]
      }
    }
  }
])

    Mongo Playground

    If you’re using Mongo version 5+, then you can use $getField to simplify the syntax a little bit, here’s how one field would look like in this syntax:

    goup: {
    $getField: {
        field: 'data',
        input: {
            '$arrayElemAt': [
                {
                    $filter: {
                        input: {
                            $ifNull: [
                                '$look',
                                [],
                            ],
                        },
                        cond: {
                            $eq: [
                                '$$this.art',
                                'group-id',
                            ],
                        },
                    },
                },
                0,
            ],
        },
    },
},
    Login or Signup to reply.
  3. 0

    You would require an aggregate operation that has a pipeline with the following key operators and stages:

    • $map: an operator to transform the look and answer arrays into documents with just mapped k and v fields, crucial for obtaining a hash map with the following operator
    • $arrayToObject: this allows the above to be possible i.e. converting an array into a single document
    • $mergeObjects: combine top level fields i.e. _id, date, name, profile together with the converted documents above
    • $replaceWith: pipeline stage to replace the root document with the specified document from above

    Overall, your pipeline should follow:

    const first = { 
   $first: {
      $split: ['$$this.art', '-']
   }
};
const keyExpression = {
   $cond: [
     { $eq: [first, 'access'] },
     'accessTime',
     first
   ]
};

const pipeline = [
    { $replaceWith: {
        $mergeObjects: [
            {  
                _id: '$_id', 
                date: '$date', 
                name: '$name', 
                profile: '$profile',
                protocol: '',
                group: '',
                called: '',
                rate: '',
                accessTime: '',
            },
            { $arrayToObject: {
                $map: {
                  input: '$look',
                  in: { k: keyExpression, v: '$$this.data' } 
                }
            } },
            { $arrayToObject: {
                $map: {
                  input: '$answer',
                  in: { k: keyExpression, v: '$$this.data' } 
                }
            } }
        ]
    } } 
]

    Mongo Playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search