0

Can this be a correct mongodb document structure where keys are generated dynamically?

{
        vehicle_id: '1234',
        month: '01-17',
        speed: {
            1: { //day 1 -> 30
                0: 50, //hour 0 -> 23
                1: 55,
                19: 90
            },
            2: {
                0: 45,
                1: 70,
                2: 100
            }
        }
}

If I need to track a certain vehicle speed for whole month each day around the clock. Would this document structure be correct and sufficient? Bear in mind that the day and hour are generated dynamically.

If so, how can I run an aggregation to get the vehicle speed across certain days/hours interval?

EDIT:

I tried this aggregation:

{$project:{
            vehicle_id: 1, month : 1,
            days: {$objectToArray: "$speed" } 
        }},
        {$unwind: '$days'},
        {$match:{
            month:'01-17',
            'days.k': {
                $eq: '30'
        }
 }},

and I get this result :

{ _id: 'db_id,
  vehicle_id: '1234',
  month: '01-17',
  days: 
   { k: '30',
     v: 
      { '0': 20,
        '1': 30,
        '2': 62,
        '3': 31,
        '4': 20,
        '5': 23,
        '6': 29, } } }

which is correct. When I try to change the match condition for the days to be:

'days.k': {$gt: '30'}

Then I should the results containing value greater than 30 which is mainly day 31. This happens but I also get the values from k: '1' -> k: '9'. Is there something wrong with the operation I am doing?

Update:

I have managed to do some modifications to get the speed across a certain interval in a month: as follows:

{$match:{
    month: '01-17',
    'days.k': {
        $in: dayValues
    }
}}

But if I add more match conditions to get another month speed but different day values, nothing is returned. 

{$match:{
        month: '01-17',
        'days.k': {
            $in: dayValues1
        }
    }},
    {$match:{
        month: '02-17',
        'days.k': {
            $in: dayValues2
        }
    }}

Any idea how to make sure each condition gets applied and all results are returned?

omarsafwany
  • 3,695
  • 8
  • 44
  • 75
  • Can you change to accumulate the speed into array of embedded document ? Something like `speed:[{day:1, hourlyspeeds:[{hour:0, speed:50}, ...]}]`. Alternately you may be split the documents across days so you will just have a document for each day `day:1, hourlyspeeds:[{hour:0, speed:50}, ...]` – s7vr Jan 16 '18 at 21:54
  • @Veeram Would this help in running an aggregation to get the speed across certain interval? – omarsafwany Jan 16 '18 at 22:01
  • Yes it will. I've [answered](https://stackoverflow.com/questions/48279637/mongo-query-nested-field-values-with-two-level-unknown-parent-keys/48285493#482854930) something similar this morning. See if it makes sense to you. – s7vr Jan 16 '18 at 22:23
  • @Veeram It helped to a certain extent. `$objectToArray`, `$unwind` are great to search inside the `speed` object but to a certain extent. If I need a certain day, it is fetched correctly. But if I need a larger interval using `$gte`, I get incorrect values. Any idea why this happens? – omarsafwany Jan 17 '18 at 11:19
  • May be your comparing against array values which will return the entire array values if there is atleast one match.. Can you please update the post with the example where it is not working? Please include both the sample documents and expected output. – s7vr Jan 17 '18 at 12:11
  • @Veeram Check the edit. – omarsafwany Jan 17 '18 at 14:07
  • `$objectToArray` outputs keys as string and your comparison is against the number text. More [here](https://stackoverflow.com/questions/6810619/how-to-explain-sorting-numerical-lexicographical-and-collation-with-examples). Its not possible to convert string into numeric in aggregation pipeline. One more reason to fix the structure to array of embedded documents. – s7vr Jan 17 '18 at 15:23
  • Is there a way to fetch documents based on key not the value? To be able to get the speed on certain days. – omarsafwany Jan 17 '18 at 15:32
  • Something like `find({"speed.30":{$exists:true}}, {"speed.30":1});` – s7vr Jan 17 '18 at 15:40
  • This would work for a single day. What if I need to get day 25 to day 30? – omarsafwany Jan 17 '18 at 15:46
  • You can enumerate keys. Not elegant but something that works `find({}, {"speed.25":1.... "speed.30":1})`. You have approach your queries on case by case basis with the current structure. – s7vr Jan 17 '18 at 15:50
  • @Veeram could you check the update and advise? – omarsafwany Jan 18 '18 at 11:57
  • You have to or the criteria together. Try `{$match:{$or:[{ month: '01-17', 'days.k': { $in: dayValues1 }}, { month: '02-17', 'days.k': { $in: dayValues2 } }]}}` – s7vr Jan 18 '18 at 13:04

0 Answers0