Term query with long value field

Elastic Stack Elasticsearch
1

I tried to search using term query with long value, but the hits were only part of whole documents.
Surprisingly, I tried to search using range query Instead of term query, then I got whole documents that I wanted.

The queries that I tried to are below.

GET txn_sql-2017.09.12/_search?sort=timestamp:asc
{
  "query": {
     "bool": {
      "filter": {
        "terms": {
          "txn_sql_tid": [
            11709120000000000,
            11709120000000002,
            11709120000000004
          ]
       }
     }
   }
 }
}

GET txn_sql-2017.09.12/_search?sort=timestamp:asc
{
 "size": 20, 
  "query": {
    "bool": {
      "filter": {
        "range": {
          "txn_sql_tid": {
            "gte": 11709120000000000,
            "lte": 11709120000000005
          }
        }
      }
    }
  }
}

And my index mapping and document are like below.

{
  "txn_sql-2017.09.12": {
    "mappings": {
      "JVM": {
        "dynamic_templates": [
          {
            "string_fields": {
              "match": "*",
              "match_mapping_type": "string",
              "mapping": {
                "index": false,
                "norms": false,
                "type": "keyword"
              }
            }
          },
          {
            "unindexed_longs": {
              "match": "*",
              "unmatch": "timestamp",
              "match_mapping_type": "long",
              "mapping": {
                "index": false,
                "norms": false,
                "type": "long"
              }
            }
          },
          {
            "unindexed_doubles": {
              "match": "*",
              "match_mapping_type": "double",
              "mapping": {
                "index": false,
                "norms": false,
                "type": "float"
              }
            }
          }
        ],
        "properties": {
          "txn_sql_tid": {
            "type": "long"
          }
          }
        }
      }
    }
  }
}

{
    "_index": "txn_sql-2017.09.12",
    "_type": "JVM",
    "_id": "AV5zZMOpGWFcK5C7kYMP",
    "_score": null,
    "_source": {
      "txn_sql_elapse_time": 0,
      "txn_sql_start_time": 1505174400473,
      "type": "JVM",
      "txn_sql_txn_hid": "81b27d038b4a75afcdf4a9b3643c0310a7dbffc3",
      "txn_sql_tid": 11709120000000002,
      "timestamp_rtm": 1505174400000,
      "tid": "1096332845530898702",
      "json_version": "v1.0",
      "tags": [
        "staging"
      ],
      "@timestamp": "2017-09-12T00:02:15.039Z",
      "user_id": "2",
      "@version": "1",
      "index_date": "2017.09.12",
      "tag": "txn_sql",
      "txn_sql_sql_hid": "155fb3499771edfeb7c82d88a372c6d9f17504cb",
      "timestamp": 1505174401075
    },
    "sort": [
      1505174401075
    ]
  }

Finally, I tried to terms aggregation with range query, then I found below response.

 GET txn_sql-2017.09.12/_search
{
  "size": 0, 
  "query": {
    "bool": {
      "filter": {
        "range": {
          "txn_sql_tid": {
            "gte": 11709120000000000,
            "lte": 11709120000000005
          }
        }
      }
    }
  },
  "aggs": {
    "NAME": {
      "terms": {
        "field": "txn_sql_tid",
        "size": 10
      }
    }
  }
}

    {
  "took": 2,
  "timed_out": false,
  "_shards": {
    "total": 10,
    "successful": 10,
    "failed": 0
  },
  "hits": {
    "total": 12,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "NAME": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": 11709120000000004,
          "doc_count": 4
        },
        {
          "key": 11709120000000000,
          "doc_count": 2
        },
        {
          "key": 11709120000000002,
          "doc_count": 2
        },
        {
          "key": 11709120000000004,
          "doc_count": 2
        },
        {
          "key": 11709120000000004,
          "doc_count": 2
        }
      ]
    }
  }
}

Why is the same value separated to other key?
Please somebody help me and explain what was wrong?

2

Which version of Elasticsearch are you running? Also could you paste the output of GET txn_sql-2017.09.12 please?

1 Like
3

My Elasticsearch's version is 5.2.1.
Do you mean you want to know about the mapping of the index?

GET txn_sql-2017.09.12
{
  "txn_sql-2017.09.12": {
    "aliases": {},
    "mappings": {
      "_default_": {
        "dynamic_templates": [
          {
            "string_fields": {
              "match": "*",
              "match_mapping_type": "string",
              "mapping": {
                "index": false,
                "norms": false,
                "type": "keyword"
              }
            }
          },
          {
            "unindexed_longs": {
              "match": "*",
              "unmatch": "timestamp",
              "match_mapping_type": "long",
              "mapping": {
                "index": false,
                "norms": false,
                "type": "long"
              }
            }
          },
          {
            "unindexed_doubles": {
              "match": "*",
              "match_mapping_type": "double",
              "mapping": {
                "index": false,
                "norms": false,
                "type": "float"
              }
            }
          }
        ],
        "properties": {
          "@timestamp": {
            "type": "date"
          },
          "@version": {
            "type": "keyword"
          },
          "event_key_id": {
            "type": "keyword"
          },
          "index_date": {
            "type": "keyword"
          },
          "tid": {
            "type": "keyword"
          },
          "timestamp": {
            "type": "date"
          },
          "timestamp_rtm": {
            "type": "date"
          },
          "txn_sql_sql_hid": {
            "type": "keyword"
          },
          "txn_sql_start_time": {
            "type": "date"
          },
          "txn_sql_tid": {
            "type": "long"
          },
          "txn_sql_txn_hid": {
            "type": "keyword"
          }
        }
      },
      "JVM": {
        "dynamic_templates": [
          {
            "string_fields": {
              "match": "*",
              "match_mapping_type": "string",
              "mapping": {
                "index": false,
                "norms": false,
                "type": "keyword"
              }
            }
          },
          {
            "unindexed_longs": {
              "match": "*",
              "unmatch": "timestamp",
              "match_mapping_type": "long",
              "mapping": {
                "index": false,
                "norms": false,
                "type": "long"
              }
            }
          },
          {
            "unindexed_doubles": {
              "match": "*",
              "match_mapping_type": "double",
              "mapping": {
                "index": false,
                "norms": false,
                "type": "float"
              }
            }
          }
        ],
        "properties": {
          "@timestamp": {
            "type": "date"
          },
          "@version": {
            "type": "keyword"
          },
          "event_key_id": {
            "type": "keyword"
          },
          "index_date": {
            "type": "keyword"
          },
          "json_version": {
            "type": "keyword",
            "index": false
          },
          "tag": {
            "type": "keyword",
            "index": false
          },
          "tags": {
            "type": "keyword",
            "index": false
          },
          "tid": {
            "type": "keyword"
          },
          "timestamp": {
            "type": "date"
          },
          "timestamp_division": {
            "type": "long",
            "index": false
          },
          "timestamp_rtm": {
            "type": "date"
          },
          "txn_sql_elapse_time": {
            "type": "long",
            "index": false
          },
          "txn_sql_sql_hid": {
            "type": "keyword"
          },
          "txn_sql_start_time": {
            "type": "date"
          },
          "txn_sql_tid": {
            "type": "long"
          },
          "txn_sql_txn_hid": {
            "type": "keyword"
          },
          "type": {
            "type": "keyword",
            "index": false
          },
          "user_id": {
            "type": "keyword",
            "index": false
          }
        }
      }
    },
    "settings": {
      "index": {
        "creation_date": "1505174477601",
        "number_of_shards": "10",
        "number_of_replicas": "0",
        "uuid": "KZXUYnr_QXSbQAyMy-W8Kw",
        "version": {
          "created": "5020199"
        },
        "provided_name": "txn_sql-2017.09.12"
      }
    }
  }
}
4

Yes, It was the concrete mappings that I was after, thanks. My hunch was that the field was not being mapped as intended but it looks like it is indeed mapped as a long on both your types.

5

Is this perhaps related to the precision of long numbers in JavaScript as your numbers are quite long? What happens if you send the numbers as strings and let Elasticsearch convert it?

GET txn_sql-2017.09.12/_search?sort=timestamp:asc
{
  "query": {
     "bool": {
      "filter": {
        "terms": {
          "txn_sql_tid": [
            "11709120000000000",
            "11709120000000002",
            "11709120000000004"
          ]
       }
     }
   }
 }
}
6

yes. I think maybe Elasticsearch converted the string to number.
The result exactly same whether sending the numbers as strings or not.

{
  "took": 63,
  "timed_out": false,
  "_shards": {
    "total": 10,
    "successful": 10,
    "failed": 0
  },
  "hits": {
    "total": 4,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "NAME": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": 11709120000000002,
          "doc_count": 2
        },
        {
          "key": 11709120000000004,
          "doc_count": 2
        }
      ]
    }
  }
}

But the actual documents count is 12.

And I tried to the same queries on Linux using curl, but the result was same.
I can't find the result that I wanted.

7

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.