0

I have this database: 

[{"cliente": {"nombre": "Cafes el amanencer"},
 "mercancia": {"envio": "Normal", "tipo": "Gaseoso", "fecha": "24/12/2003", "peso": 21, "volumen": 43, "origen": "Cadiz", "destino": "Castellon"},
 "vagon": {"id": 1330, "volumen": 202, "peso": 433 }},{"cliente": {"nombre": "Electronica Chispas"}, "mercancia": {"envio": "Normal", "tipo": "Liquido", "fecha": "08/02/2005", "peso": 17, "volumen": 24, "origen": "San Sebastian", "destino": "Orense"}, "vagon": {"id": 1290, "volumen": 111, "peso": 464 }},{"cliente": {"nombre": "Pepe Gotera y Otilio"}, "mercancia": {"envio": "Economico", "tipo": "Contaminante", "fecha": "14/03/2003", "peso": 2, "volumen": 49, "origen": "Santander", "destino": "Burgos"}, "vagon": {"id": 1100, "volumen": 323, "peso": 258 }},{"cliente": {"nombre": "Coches Coco"}, "mercancia": {"envio": "Normal", "tipo": "Explosivo", "fecha": "11/07/2002", "peso": 23, "volumen": 51, "origen": "Avila", "destino": "Santa Cruz de Tenerife"}, "vagon": {"id": 1183, "volumen": 171, "peso": 439 }},{"cliente": {"nombre": "Infraestructuras Fracturas"}, "mercancia": {"envio": "Urgente primera hora", "tipo": "Fragil", "fecha": "07/08/2000", "peso": 53, "volumen": 3, "origen": "Tarragona", "destino": "Soria"}, "vagon": {"id": 1454, "volumen": 408, "peso": 101 }}]

There are more data but to solve this, it will do. So I am trying to look for 2 things(queries):

The 1st one: A list with the 3 "destinos" with most sent "mercancias" and the number of those, for an "origen" and "fecha", in this case, the one I use in the query: 

 db.mercancias.aggregate(['$project':{origen:'origen', destino:'destino', anio:'fecha'},
 {$match: 'origen':'San Sebastian', $year:{'fecha':08/02/2005}},
 {$group:{'_id':{'$destino', '$anio'}}},{ maximo:{$max:{$count:{'_id':'envio'}}},{$limit:3}}])

I am not sure where I am failing with the query or if it really is something related with syntax.

In the second one, I am trying to get a list of "mercancias" with "destino" close to some coordenates (100km max), ordered by order of distance(I am quite lost with this one, I am not sure how to determine the coordenates, but I have programmed in python the geoNear, so the problem is mainly in the query). I also want to be it an aggregate query, but as I have said, I am quite lost at how I am getting it wrong. 

db.mercancias.find([{$geoNear:{'near:{'type':'Point', 'coordinates':coordinates}maxDistance':100,'distanceField':'dist.calculated'}}])

Anything you think I need to add the question to make it clearer, I will happily edit it.

Improve this question

2 Answers 2

Reset to default
1

You have many syntax errors. First thing you have to do is get the syntax right. I'll try to list down all the issues with possible fix.

Project:

'$project':{origen:'origen', destino:'destino', anio:'fecha'}

1.Missing opening and closing braces.

2.Missing the dot notation to access the embedded fields along with variable reference $.

Fix:

{'$project':{origen:'$mercancia.origen', destino:'$mercancia.destino'; anio:'$mercancia.fecha', envio:'$mercancia.envio'}}

Match:

 {$match: 'origen':'San Sebastian', $year:{'fecha':08/02/2005}}

1.You are combining query operator with aggregation operator which is not allowed inside regular match.

2.Missing the opening braces for query criteria.

3.Supported date format is MM/DD/YYYY.

Fix:

1.Use $match with $expr.

2.Add opening and closing braces for query criteria.

3.Change the string date format to store as 12/24/2003.

{$match:{
  'origen':'San Sebastian', 
  $expr:{$eq:[{$year:{$dateFromString:{dateString:"$anio"}}}, input year ]}
}}

Group:

{$group:{ '_id':{'$destino', '$anio'}}},{ maximo:{$max:{$count:{'_id':'envio'}}}
  1. Group key should be key value pair.
  2. Cant combine $max with $count. $max is a aggregation operator whereas $count is a aggregation stage.

Fix: (Not sure what you are intending here but it looks like you are wanting to group by destino & envio and count their occurrences and sort by count descending.

{$group: {_id:{'destino':'$destino', 'envio':'$envio'}, count:{$sum:1} }},
{$count:{sum:-1}}

Putting it all together.

data:

db.mercancias.insert([
    {"cliente": {"nombre": "Cafes el amanencer"}, "mercancia": {"envio": "Normal", "tipo": "Gaseoso", "fecha": "12/24/2003", "peso": 21, "volumen": 43, "origen": "Cadiz", "destino": "Castellon"}, "vagon": {"id": 1330, "volumen": 202, "peso": 433 }},
    {"cliente": {"nombre": "Electronica Chispas"}, "mercancia": {"envio": "Normal", "tipo": "Liquido", "fecha": "02/08/2005", "peso": 17, "volumen": 24, "origen": "San Sebastian", "destino": "Orense"}, "vagon": {"id": 1290, "volumen": 111, "peso": 464 }},
    {"cliente": {"nombre": "Pepe Gotera y Otilio"}, "mercancia": {"envio": "Economico", "tipo": "Contaminante", "fecha": "03/14/2003", "peso": 2, "volumen": 49, "origen": "Santander", "destino": "Burgos"}, "vagon": {"id": 1100, "volumen": 323, "peso": 258 }},
    {"cliente": {"nombre": "Coches Coco"}, "mercancia": {"envio": "Normal", "tipo": "Explosivo", "fecha": "07/11/2002", "peso": 23, "volumen": 51, "origen": "Avila", "destino": "Santa Cruz de Tenerife"}, "vagon": {"id": 1183, "volumen": 171, "peso": 439 }},
    {"cliente": {"nombre": "Infraestructuras Fracturas"}, "mercancia": {"envio": "Urgente primera hora", "tipo": "Fragil", "fecha": "08/07/2000", "peso": 53, "volumen": 3, "origen": "Tarragona", "destino": "Soria"}, "vagon": {"id": 1454, "volumen": 408, "peso": 101 }}
])

query:

db.mercancias.aggregate([
   {'$project':{origen:'$mercancia.origen', destino:'$mercancia.destino', anio:'$mercancia.fecha', envio:'$mercancia.envio'}},
   {$match:{
      'origen':'San Sebastian', 
      $expr:{$eq:[{$year:{$dateFromString:{dateString:"$anio"}}}, 2005]}
    }},
   {$group: {_id:{'destino':'$destino', 'envio':'$envio'}, count:{$sum:1} }},
   {$sort:{count:-1}},
   {$limit:3}
]);

output:

 { "destino":"Orense", "envio":"Normal", "count" : 1}
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you so much! Really well explained, step by step and logical. Thank you!
1

For the first one, I think you have to use the project stage to have all of your desired data in the same level, after, you can use $fecha in your $year stage

Improve this answer

2 Comments

Yes, I realised it some minutes ago (I edited the question). Regarding what you said of $fecha , I need to take the year of that date, not the date itself!
If you change your date format, it will take the year automatically, check this : docs.mongodb.com/manual/reference/operator/aggregation/year

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.