ROW_NUMBER in ES SQL

Elastic Stack Kibana
1

Hello,

does the command ROW_NUMBER() exist for ES SQL?
When not, is there something equivalent to it?

Can someone do an example, please?

With best regards

2

Can you explain a little more what you'd like to achieve with this?

3

of course!
After performing the ES SQL query in a Canvas workspace, we want that the result of the search is getting displayed in the preview of a datatable with row numbers.

4

row_numbers
row_numbers1081×526 15.7 KB

5

You could order by customer_id, but the results of the sum won't translate into rows so I don't think that really translates here.

6

In the end, we want to add a column from 1-n to our ES SQL result where n is the numbers of elements found in the ES SQL query.

7

There's no concept of a row ID in Elasticsearch.
And I understand the outcome, I just don't see why it'd matter when you can sort on the customer ID? Unless there's something else specific you want to do with the row ID I am not aware of.

8

We have significant issues ordering our data table in the Canvas workpad accordingly to the timestamps.

I explain the full problem here: Link
If this problem is solved then we are happy and we can continue our work.

In order to make the data table only present elements which lie between two dates, we thought to take these steps:

  1. Adjust the ES SQL code so that the preview of the result is what we would like to get
  2. We add an additional column so that timestamp1 and timestamp2 (timestamp1 < timestamp2) are getting numbers which are number1<number2
  3. In the expression editor, we set 'filterColumn' to the column, which we created at step 2

We hope that you understand our request.
We just want to filter our data table according to a given timespan.

With best regards

9

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