Home > database >  ElasticSearch - Adjacency matrix & Sum
ElasticSearch - Adjacency matrix & Sum

Time:02-14

I'm currently learning Elastic, I've created this dataset on french presidentials elections from 1965 to 2017 and I want to query the sum of all the documents matching "tour" = 1 and "election" = 1974.

I've done this, but it's not working, what I did wrong ?

{
  "size": 0,
  "aggs": {
    "somme_blancs_nuls": {
      "sum": {
        "field": "blancs_nuls",
        "aggs": {
          "interactions": {
            "adjacency_matrix": {
              "filters": {
                "grpA": {
                  "match": {
                    "tour": 1
                  }
                },
                "grpB": {
                  "match": {
                    "election": 1974
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Here's my mapping and a sample document.

// Mapping
{
    "properties": {
        "election": {
            "type": "integer"
        },
        "tour": {
            "type": "integer"
        },
        "code": {
            "type": "text"
        },
        "libelle": {
            "type": "text",
            "fields": {
                "keyword": {
                    "type":"keyword"
                }
            }
        },
        "inscrits": {
            "type": "long"
        },
        "votants": {
            "type": "long"
        },
        "exprimes": {
            "type": "long"
        },
        "abstentions": {
            "type": "long"
        },
        "blancs_nuls": {
            "type": "long"
        },
        "candidats": {
            "type": "nested",
            "properties": {
                "nom": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type":"keyword"
                        }
                    }
                },
                "parti": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type":"keyword"
                        }
                    }
                },
                "votes": {
                    "type": "long"
                }
            }
        }
    }
}
// Sample
{
    "election": 1965,
    "tour": 1,
    "code": "1",
    "libelle": "AIN",
    "inscrits": 206496,
    "votants": 166986,
    "exprimes": 165555,
    "abstentions": 39510,
    "blancs_nuls": 1431,
    "candidats": [
        {
        "nom": "François MITTERRAND",
        "parti": "CIR",
        "votes": 50418
        },
        {
        "nom": "Charles DE GAULLE",
        "parti": "UNR",
        "votes": 71246
        },
        {
        "nom": "Jean LECANUET",
        "parti": "MRP",
        "votes": 30416
        },
        {
        "nom": "Jean-Louis TIXIER-VIGNANCOUR",
        "parti": "EXD",
        "votes": 8317
        },
        {
        "nom": "Pierre MARCILHACY",
        "parti": "DVD",
        "votes": 3006
        },
        {
        "nom": "Marcel BARBU",
        "parti": "DIV",
        "votes": 2152
        }
    ]
}

Thanks in advance :)

CodePudding user response:

You can use a combination of the search query with aggregations

You need to use a boolean query to find all the documents matching "tour" = 1 and "election" = 1974, and then use sum aggregation to find the sum of blancs_nuls field on the matching documents

{
    "size":0,
    "query": {
        "bool": {
            "must": [
                {
                    "match": {
                        "tour": 1
                    }
                },
                {
                    "match": {
                        "election": 1974
                    }
                }
            ]
        }
    },
    "aggs": {
        "balancs_sum": {
            "sum": {
                "field": "blancs_nuls"
            }
        }
    }
}

Search Response will be

"aggregations": {
        "balancs_sum": {
            "value": 2862.0
        }
    }
  • Related