SELECT the last three rows in DATA with IN list

Elastic Stack Elasticsearch
1

Trying to select the last 3 rows of a group. If I run this query

POST /_sql?format=txt
{
  "query": "SELECT \"Samples.AccumulatedTime.@@data\",\"Samples.AccumulatedTime.name\",deviceName,timestamp FROM \"mtconnect-demo\" WHERE \"Samples.AccumulatedTime.name\" IN ('p1TotalOperatingTime', 'p1TotalRunningTime', 'p1TotalCuttingTime') AND deviceName = 'OKUMA.MachiningCenter.MU6300' ORDER BY timestamp DESC"
}

I get this result which I expect

Samples.AccumulatedTime.@@data|Samples.AccumulatedTime.name|         deviceName         |       timestamp        
------------------------------+----------------------------+----------------------------+------------------------
3949148                       |p1TotalCuttingTime          |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:22.255Z
45546100                      |p1TotalRunningTime          |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:22.243Z
4973127                       |p1TotalOperatingTime        |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:22.229Z
3949146                       |p1TotalCuttingTime          |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:20.069Z
45546098                      |p1TotalRunningTime          |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:20.043Z
4973125                       |p1TotalOperatingTime        |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:20.033Z
3949143                       |p1TotalCuttingTime          |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:17.506Z
45546095                      |p1TotalRunningTime          |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:17.496Z
4973122                       |p1TotalOperatingTime        |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:17.485Z
3949141                       |p1TotalCuttingTime          |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:15.259Z
45546092                      |p1TotalRunningTime          |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:15.233Z
4973120                       |p1TotalOperatingTime        |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:15.222Z

So I figure I would do a TOP 1 to get the top result of each in my list

POST /_sql?format=txt
{
  "query": "SELECT TOP 1 \"Samples.AccumulatedTime.@@data\",\"Samples.AccumulatedTime.name\",deviceName,timestamp FROM \"mtconnect-demo\" WHERE \"Samples.AccumulatedTime.name\" IN ('p1TotalOperatingTime', 'p1TotalRunningTime', 'p1TotalCuttingTime') AND deviceName = 'OKUMA.MachiningCenter.MU6300' ORDER BY timestamp DESC"
}

But that will return just a single line of

Samples.AccumulatedTime.@@data|Samples.AccumulatedTime.name|         deviceName         |       timestamp        
------------------------------+----------------------------+----------------------------+------------------------
3949148                       |p1TotalCuttingTime          |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:22.255Z

And not the 3 items in my IN list

How can I get the TOP 1 of each row of
p1TotalOperatingTime, p1TotalRunningTime, p1TotalCuttingTime?

2

DSL like this:

POST index1/_search
{
  "size": 0,
  "aggs": {
    "groupby": {
      "terms": {
        "field": "Samples.AccumulatedTime.name",
        "size": 3
      },
      "aggs": {
        "top1": {
          "top_hits": {
            "size": 1,
            "sort": [
              {
                "timestamp": {
                  "order": "desc"
                }
              }
            ]
          }
        }
      }
    }
  }
}
1 Like
3

Thanks for the direction but I get this

"error" : {
    "root_cause" : [
      {
        "type" : "illegal_argument_exception",
        "reason" : "Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [Samples.AccumulatedTime.name] in order to load field data by uninverting the inverted index. Note that this can use significant memory."
      }
    ],
    "type" : "search_phase_execution_exception",
    "reason" : "all shards failed",
    "phase" : "query",
    "grouped" : true,
    "failed_shards" : [
      {
        "shard" : 0,
        "index" : "mtconnect-demo",
        "node" : "N_i5Gs8zQsWDhwgQZq5Thw",
        "reason" : {
          "type" : "illegal_argument_exception",
          "reason" : "Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [Samples.AccumulatedTime.name] in order to load field data by uninverting the inverted index. Note that this can use significant memory."
        }
      }
    ],
    "caused_by" : {
      "type" : "illegal_argument_exception",
      "reason" : "Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [Samples.AccumulatedTime.name] in order to load field data by uninverting the inverted index. Note that this can use significant memory.",
      "caused_by" : {
        "type" : "illegal_argument_exception",
        "reason" : "Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [Samples.AccumulatedTime.name] in order to load field data by uninverting the inverted index. Note that this can use significant memory."
      }
    }
  },
  "status" : 400
}

I am an ELK novice so any help would be great,.

4

terms agg must be used with keyword type，so you need set Samples.AccumulatedTime.name as keyword type

5

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