Very complex filter (like group by)?

[Ely_96 (Elisa)]

Hi Guys,

I need an help because I cannot find a way to filter a simple datatable.

My index pattern consists of the following csv:

ID;Class;User Age
1234;A;18
1235;A;23
1236;B;25

I would like to set a filter in the top left bar to see in my datatable only the classes (second column of the CSV) that have all "User Age"> 20. So in my example I should only see class B.

If I set a filter like:
User Age > 20 I can see both ID: 1235 and 1236 but I don't want to see ID 1235 because class A has a User Age <20 (ID 1234).

Could anyone please help me?

Thanks in advance
Ely

[stephenb (Stephen Brown)]

Hi @Ely_96 Yup its a little harder and you would use a PIvot Transform and then you can do what you want.

You can look up the docs here there are samples and everything

And you can just dive in... and try it through Kibana -> Stack Management -> Transform

Below is a complete solution through the API with some assumptions
You will need to create a Data View for each of the initial index and the resulting transform index.

Initial Class Data

DELETE discuss-class
PUT discuss-class
{
  "mappings": {
    "properties": {
      "id": {
        "type": "keyword"
      },
      "class": {
        "type": "keyword"
      },
      "age": {
        "type": "integer"
      }
    }
  }
}

POST discuss-class/_bulk
{ "index":{ } }
{ "id" : "1234", "class" : "101", "age" : 18}
{ "index":{ } }
{ "id" : "1235", "class" : "101", "age" : 21}
{ "index":{ } }
{ "id" : "1236", "class" : "201", "age" : 24}
{ "index":{ } }
{ "id" : "1237", "class" : "201", "age" : 35}
{ "index":{ } }
{ "id" : "1238", "class" : "301", "age" : 24}
{ "index":{ } }
{ "id" : "1239", "class" : "301", "age" : 25}
{ "index":{ } }
{ "id" : "1240", "class" : "301", "age" : 25}
{ "index":{ } }
{ "id" : "1241", "class" : "301", "age" : 36}
{ "index":{ } }
{ "id" : "1242", "class" : "301", "age" : 20}
{ "index":{ } }
{ "id" : "1243", "class" : "102", "age" : 25}
{ "index":{ } }
{ "id" : "1244", "class" : "102", "age" : 36}
{ "index":{ } }
{ "id" : "1224", "class" : "102", "age" : 18}

Now the transform

The transform destination index

DELETE discuss-class-transform-index

GET discuss-class-transform-index

PUT discuss-class-transform-index
{
  "mappings": {
    "properties": {
      "id": {
        "type": "keyword"
      },
      "id_value_count": {
        "type": "long"
      },
      "class": {
        "type": "keyword"
      },
      "age_min": {
        "type": "integer"
      },
      "age_max": {
        "type": "integer"
      }
    }
  }
}

Now the transform ... again please feel free to go to the GUI and do the same... careful with the field names. Note I did min and max age you could do average etc...

DELETE _transform/discuss-class-transform

GET _transform/discuss-class-transform

PUT _transform/discuss-class-transform
{
  "source": {
    "index": [
      "discuss-class"
    ],
    "query": {
      "match_all": {}
    }
  },
  "dest": {
    "index": "discuss-class-transform-index"
  },
  "pivot": {
    "group_by": {
      "class": {
        "terms": {
          "field": "class"
        }
      }
    },
    "aggregations": {
      "age_max": {
        "max": {
          "field": "age"
        }
      },
      "id_value_count": {
        "value_count": {
          "field": "id"
        }
      },
      "age_min": {
        "min": {
          "field": "age"
        }
      }
    }
  },
  "settings": {}
}

Now Key you have to start / run the transform

POST _transform/discuss-class-transform/_start

Now the data is there you can do a quick search if you want but create a Data View

GET discuss-class-transform-index/_search

And now you can do all sorts if easy stuff

Now All the Classes

Now classes with min Age >= 20

The Filter

The Result