Ejemplos queries de aggregations con Multas de Montevideo

queries.json

POST montevideo/

# Importante crear el mapping antes para que las "coordinates" las tome como geopoint.
# Tambien utilizo not_analyzed en varios campos de texto para no procesaros y agregarlos con valores exactos.
PUT montevideo/_mapping/multa
{
  "multa": {
    "properties": {
      "coordinates": {
        "type": "geo_point"
      },
      "neighbourhood": {
        "properties": {
          "name": {
            "type": "string",
            "index": "not_analyzed"
          }
        }
      },
      "ordinance": {
        "properties": {
          "description": {
            "type": "string",
            "index": "not_analyzed"
          },
          "paragraph": {
            "type": "string",
            "index": "not_analyzed"
          }
        }
      },
      "street_name": {
        "type": "string",
        "index": "not_analyzed"
      },
      "vehicle_type": {
        "type": "string",
        "index": "not_analyzed",
        "fields": {
          "processed": {
            "type": "string",
            "index": "analyzed"
          }
        }
      },
      "intersection": {
        "properties": {
          "secondary_street_name": {
            "type": "string",
            "index": "not_analyzed"
          }
        }
      }
    }
  }
}

# Traer el mapping de multa (para confirmar la creacion)
GET montevideo/multa/_mapping

# Conteo del numero de multas
GET montevideo/multa/_search?search_type=count

# Search basico para verificar los datos
POST montevideo/multa/_search
{
  "query": {
    "match": {
      "ordinance.description": "NO RESPETAR CARTEL DE PARE (D.622)"
    }
  }
}

# Aggregation simple con todas las descripciones de las multas
POST montevideo/multa/_search?search_type=count
{
  "aggs": {
    "agg_example": {
      "terms": {
        "field": "ordinance.description",
        "size": 100
      }
    }
  }
}

# Aggreation por tipo de vehiculo
POST montevideo/multa/_search?search_type=count
{
  "aggs": {
    "agg_example": {
      "terms": {
        "field": "vehicle_type",
        "size": 1000
      }
    }
  }
}

# Suma del total de UR
POST montevideo/multa/_search?search_type=count
{
  "aggs": {
    "agg_example": {
      "sum": {
        "field": "ordinance.ur_amount"
      }
    }
  }
}

# Por mes por total de UR
POST montevideo/multa/_search?search_type=count
{
  "aggs": {
    "amount_date": {
      "date_histogram": {
        "field": "date",
        "interval": "month"
      },
      "aggs": {
        "sum_per_month": {
          "sum": {
            "field": "ordinance.ur_amount"
          }
        }
      }
    }
  }
}

# Por cada barrio ver multas significativas
POST montevideo/multa/_search?search_type=count
{
  "aggs": {
    "por_barrio": {
      "terms": {
        "field": "neighbourhood.name",
        "size": 100
      },
      "aggs": {
        "amount_date": {
          "significant_terms": {
            "field": "ordinance.description",
            "size": 5
          }
        }
      }
    }
  }
}

# Por cada multa ver barrios significativos
POST montevideo/multa/_search?search_type=count
{
  "aggs": {
    "por_ordinance": {
      "terms": {
        "field": "ordinance.description",
        "size": 1000
      },
      "aggs": {
        "amount_date": {
          "terms": {
            "field": "neighbourhood.name",
            "size": 5
          }
        }
      }
    }
  }
}

# Por cada barrio ver estadisticas de monto
POST montevideo/multa/_search?search_type=count
{
  "aggs": {
    "por_barrio": {
      "terms": {
        "field": "neighbourhood.name"
      },
      "aggs": {
        "amount_nei": {
          "extended_stats": {
            "field": "ordinance.ur_amount"
          }
        }
      }
    }
  }
}

# Top calles ver multas significativas
POST montevideo/multa/_search?search_type=count
{
  "aggs": {
    "por_calle": {
      "terms": {
        "field": "street_name",
        "size": 40
      },
      "aggs": {
        "por_multa": {
          "terms": {
            "field": "ordinance.description",
            "size": 5
          }
        }
      }
    }
  }
}

# Top multas ver calles significativas
POST montevideo/multa/_search?search_type=count
{
  "aggs": {
    "por_multa": {
      "terms": {
        "field": "ordinance.description",
        "size": 40
      },
      "aggs": {
        "por_calle": {
          "significant_terms": {
            "field": "street_name",
            "size": 5
          }
        }
      }
    }
  }
}

# Top multas ver calles significativas
POST montevideo/multa/_search?search_type=count
{
  "aggs": {
    "por_multa": {
      "terms": {
        "field": "ordinance.description",
        "size": 40
      },
      "aggs": {
        "por_calle": {
          "terms": {
            "field": "neighbourhood.name",
            "size": 5
          }
        }
      }
    }
  }
}

# Top multas + calles secundarias
POST montevideo/multa/_search?search_type=count
{
  "aggs": {
    "por_multa": {
      "terms": {
        "field": "street_name",
        "size": 40
      },
      "aggs": {
        "por_calle": {
          "terms": {
            "field": "intersection.secondary_street_name",
            "size": 5
          }
        }
      }
    }
  }
}


# Top calles secundarias + principales
POST montevideo/multa/_search?search_type=count
{
  "aggs": {
    "por_multa": {
      "terms": {
        "field": "intersection.secondary_street_name",
        "size": 40
      },
      "aggs": {
        "por_calle": {
          "terms": {
            "field": "street_name",            "size": 5
          }
        }
      }
    }
  }
}

# Sub-sub-sub-sub aggregations
POST montevideo/multa/_search?search_type=count
{
  "aggs": {
    "root_aggregation": {
      "terms": {
        "field": "neighbourhood.name"
      },
      "aggs": {
        "inner_aggregation": {
          "terms": {
            "field": "ordinance.description"
          },
          "aggs": {
            "inner_aggregation2": {
              "date_histogram": {
                "field": "date",
                "interval": "year"
              },
              "aggs": {
                "inner_aggregation3": {
                  "terms": {
                    "field": "vehicle_type"
                  },
                  "aggs": {
                    "inner_aggregation4": {
                      "terms": {
                        "field": "street_name"
                      },
                      "aggs": {
                        "inner_aggregation5": {
                          "terms": {
                            "field": "intersection.secondary_street_name"
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}