Add more sort on specific fields


(Ge Gary) #1

When the first field is equal, the second field is not equal but not compared
my query :

GET /filebeat.2018-12-20/_search
    {
      "size": 20,
      "query": {
        "bool": {
          "must": [
            {
              "prefix": {
                "source": "/var/lib/docker/containers/64a20413d8c6ff497abe357e3e265620e5f64d97ee1d2eb50501150039b5f2a1/64a20413d8c6ff497abe357e3e265620e5f64d97ee1d2eb50501150039b5f2a1-json.log"
              }
            }
          ],
          "filter": {
            "range": {
              "@timestamp": {
                "gte": "2018-12-20",
                "lte": "now"
              }
            }
          }
        }
      },
      "sort": [
        {
          "@timestamp": {
            "order": "asc"
          }
        },
        {
          "offset": {
            "order": "asc"
          }
        }
      ]
    }

the actual result:

{
...
        "@timestamp":"2018-12-20T06:32:25.274Z",
        "offset":1155,
...
},
{
...
        "@timestamp":"2018-12-20T06:32:25.274Z",
        "offset":936,
...
}

the expected result:

{
...
        "@timestamp":"2018-12-20T06:32:25.274Z",
        "offset":936,
...
},
{
...
        "@timestamp":"2018-12-20T06:32:25.274Z",
        "offset":1155,
...
}

(David Pilato) #2

Please don't post images of text as they are hardly readable and not searchable.

Instead paste the text and format it with </> icon. Check the preview window.

Could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.


(Ge Gary) #3

it also need return when fromat it with </> icon...:stuck_out_tongue_winking_eye:


(David Pilato) #5

Not sure what your problem is. Here is what I'm getting:

DELETE test 
PUT test/_doc/1
{
  "@timestamp": "2018-12-20T06:32:25.274Z",
  "offset": 900
}
PUT test/_doc/2
{
  "@timestamp": "2018-12-20T06:32:25.274Z",
  "offset": 1000
}
GET test/_search
{
  "sort": [
    {
      "@timestamp": {
        "order": "asc"
      }
    },
    {
      "offset": {
        "order": "asc"
      }
    }
  ]
}
{
  "took" : 5,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 2,
    "max_score" : null,
    "hits" : [
      {
        "_index" : "test",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : null,
        "_source" : {
          "@timestamp" : "2018-12-20T06:32:25.274Z",
          "offset" : 900
        },
        "sort" : [
          1545287545274,
          900
        ]
      },
      {
        "_index" : "test",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : null,
        "_source" : {
          "@timestamp" : "2018-12-20T06:32:25.274Z",
          "offset" : 1000
        },
        "sort" : [
          1545287545274,
          1000
        ]
      }
    ]
  }
}

(Ge Gary) #6

Thank you for your example. I tried it. If offset type is keyword, it cannot be sorted. But I use long, it work right:

PUT test1/_mapping/doc
{
  "properties": {
    "offset": {
      "type": "long"
    },
     "@timestamp": {
            "type": "date",
            "format": "dateOptionalTime"
          }
  }
}

PUT test1/doc/1
{
  "@timestamp": "2018-12-20T06:32:25.274Z",
  "offset": 900
}
PUT test1/doc/2
{
  "@timestamp": "2018-12-20T06:32:25.274Z",
  "offset": 1000
}
PUT test1/doc/3
{
  "@timestamp": "2018-12-20T06:32:24.914Z",
  "offset": 865
}

GET test1/_search
{
  "sort": [
    {
      "@timestamp": {
        "order": "asc"
      }
    },
    {
      "offset": {
        "order": "asc"
      }
    }
  ]
}

the result is right:

{
      "took": 2,
      "timed_out": false,
      "_shards": {
        "total": 5,
        "successful": 5,
        "failed": 0
      },
      "hits": {
        "total": 3,
        "max_score": null,
        "hits": [
          {
            "_index": "test1",
            "_type": "doc",
            "_id": "3",
            "_score": null,
            "_source": {
              "@timestamp": "2018-12-20T06:32:24.914Z",
              "offset": 865
            },
            "sort": [
              1545287544914,
              865
            ]
          },
          {
            "_index": "test1",
            "_type": "doc",
            "_id": "1",
            "_score": null,
            "_source": {
              "@timestamp": "2018-12-20T06:32:25.274Z",
              "offset": 900
            },
            "sort": [
              1545287545274,
              900
            ]
          },
          {
            "_index": "test1",
            "_type": "doc",
            "_id": "2",
            "_score": null,
            "_source": {
              "@timestamp": "2018-12-20T06:32:25.274Z",
              "offset": 1000
            },
            "sort": [
              1545287545274,
              1000
            ]
          }
        ]
      }
    }

offset filed use the type of keyword:

PUT test/_mapping/doc
{
  "properties": {
    "offset": {
      "type": "keyword",
      "store": true
    },
    "@timestamp": {
      "type": "date",
      "format": "dateOptionalTime"
    }
  }
}

PUT test/doc/1
{
  "@timestamp": "2018-12-20T06:32:25.274Z",
  "offset": 900
}
PUT test/doc/2
{
  "@timestamp": "2018-12-20T06:32:25.274Z",
  "offset": 1000
}
PUT test/doc/3
{
  "@timestamp": "2018-12-20T06:32:24.914Z",
  "offset": 865
}

the result is not right:

{
  "took": 2,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 3,
    "max_score": null,
    "hits": [
      {
        "_index": "test",
        "_type": "doc",
        "_id": "3",
        "_score": null,
        "_source": {
          "@timestamp": "2018-12-20T06:32:24.914Z",
          "offset": 865
        },
        "sort": [
          1545287544914,
          "865"
        ]
      },
      {
        "_index": "test",
        "_type": "doc",
        "_id": "2",
        "_score": null,
        "_source": {
          "@timestamp": "2018-12-20T06:32:25.274Z",
          "offset": 1000
        },
        "sort": [
          1545287545274,
          "1000"
        ]
      },
      {
        "_index": "test",
        "_type": "doc",
        "_id": "1",
        "_score": null,
        "_source": {
          "@timestamp": "2018-12-20T06:32:25.274Z",
          "offset": 900
        },
        "sort": [
          1545287545274,
          "900"
        ]
      }
    ]
  }
}

(David Pilato) #7

It's correct. When you set it to keyword, it's considered as a Text then.

  • 1000 is after 900.
  • "900" is after "1000".

(system) closed #8

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