CODEWITHSUNDEEP
elasticsearch
Leo Zhang
Leo Zhang

Reputation: 3230

How to import data that has big int column into elastic search?

I have a table in PostgreSQL with a column which type is numeric(40, 0).

I'd like to be able to filter and sort on this field.

The problem is when the data is queried from PostgreSQL, and then converted into json, the big integer values are converted into "text", and will be recognized as "text" type in elastic search, which can't be used for filtering and sorting.

The long type from elastic search can only support integers up to 10^20, but I'd like to support integers up to 10^40.

How to import such data?

Upvotes: 0

Views: 328

Answers (2)

Tung Luong Thanh
Tung Luong Thanh

Reputation: 473

Did you try with sum aggregation?

Upvotes: 0

James Pittiglio
James Pittiglio

Reputation: 641

While I don't have a solution for adding support for numbers in the 10 duodecillion+ range, I wanted to point out that when using dynamic mapping the string of digits you are sending is being stored as both a text and keyword data type. Thus, if you wanted to perform sorting or filtering, you could operate on the keyword field.

For example:

PUT random_big

POST random_big/_doc
{
  "num": "10000000000000000000000000000000000000000"
}
POST random_big/_doc
{
  "num": "10000000000000000000000000000000000000001"
}
POST random_big/_doc
{
  "num": "10000000000000000001000000000000000000000"
}

Checking the mapping (GET random_big/_mapping) shows:

{
  "random_big" : {
    "mappings" : {
      "_doc" : {
        "properties" : {
          "num" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          }
        }
      }
    }
  }
}

And sorting can be performed as follows:

GET random_big/_search
{
  "sort": [
    {
      "num.keyword": {
        "order": "asc"
      }
    }
  ]
}

Upvotes: 2

Related Questions