Term query with long value field


(κΆŒμ„±μ›) #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?


(Colin Goodheart-Smithe) #2

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


(κΆŒμ„±μ›) #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"
      }
    }
  }
}

(Colin Goodheart-Smithe) #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.


(Christian Dahlqvist) #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.


(system) #7

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