Query which return number range

[Serhii_Maistruk]

Hi there,
maybe someone has an idea if i'm able to do following query using kibana vizualization:
I have huge index tag filled with phone numbers, and i need to check if they are "sequential".
For example, we have next data:
phone_number: "1234567890"
phone_number: "1234567891"
phone_number: "1234567892"
phone_number: "1234567893"
phone_number: "1234567894"
phone_number: "1234567895"
phone_number: "1234567896"
phone_number: "1234567897"
phone_number: "1234567898"
phone_number: "1234567899"
i need to build a query which would return 12345678*.
Thank you in advance.

[nickpeihl]

Hi @Serhii_Maistruk.

I think you could use a histogram aggregation that uses an interval of 10. This will count the number of documents for a range rounded down to the nearest 10. Then you could look for responses that have a doc_count of 10 which might indicate that a complete series of sequential phone numbers exist.

However, you may have duplicate phone numbers in your index. So you may need an additional subaggregation for cardinality. In this case, you only want to count unique phone numbers and look for buckets that have a unique_count of 10. I pasted an example from Kibana's DevConsole below.

PUT phone
{
  "mappings": {
    "properties": {
      "phone_number": {
        "type": "integer"
      }
    }
  }
}



POST phone/_bulk
{"create":{}}
{"phone_number": "1234567890"}
{"create":{}}
{"phone_number": "1234567891"}
{"create":{}}
{"phone_number": "1234567892"}
{"create":{}}
{"phone_number": "1234567893"}
{"create":{}}
{"phone_number": "1234567894"}
{"create":{}}
{"phone_number": "1234567895"}
{"create":{}}
{"phone_number": "1234567896"}
{"create":{}}
{"phone_number": "1234567897"}
{"create":{}}
{"phone_number": "1234567898"}
{"create":{}}
{"phone_number": "1234567899"}
{"create":{}}
{"phone_number": "1234567885"}
{"create":{}}
{"phone_number": "1234567886"}
{"create":{}}
{"phone_number": "1234567887"}
{"create":{}}
{"phone_number": "1234567888"}
{"create":{}}
{"phone_number": "1234567889"}




POST /phone/_search?size=0
{
  "aggs": {
    "phone_ranges": {
      "histogram": {
        "field": "phone_number",
        "interval": 10
      },
      "aggs": {
        "unique_count": {
          "cardinality": {
            "field": "phone_number"
          }
        }
      }
    }
  }
}