Mongodb complex Aggregate query not working

Question

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.

Answer 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}

Answer 2

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