Aggregate the data such that there's a new column which depends on the original Question?

Dear Team,

Below is my requirement.

I have 2 fields in my system (1. Category & 2. Questions)

Here is my requirement goes. and below is my data.

My requirement. I need to Manually add one more new field called Sub category by writing elastic search query which is not there in the system. with the below criteria.

  1. If my question is "I need to get parcel as soon as possible" AND Category is "Expediate" Then it should be "Express" as subcategory
  2. If my question is "I need parcel after 2 days" AND Category is "Medium" Then it should be "MIDLEVEL" as subcategory
    1. If my question is "I need today itself " AND Category is "Super fast " Then it should be "HIGHER" as subcategory

Below should be my output

  1. Need Questions field aggregation

  2. Need New field as subcategory aggregation

Expected result : aggregate the data such that there's a new column which depends on the original Question

image

Hey, it sounds like this is a good use case for scripted fields: https://www.elastic.co/guide/en/kibana/current/scripted-fields.html

You can put the custom logic you noted down in your question into painless code and return the appropriate subcategory string, then add it as a scripted field named "subcategory" in the index pattern. Once you've done that, you can use it like any other field (e.g. to do a terms aggregation on it)

Hi Joe,

This will not solve my issue.

My requirement is I have some around 50k questions in that need to pick up only 10 questions which is static.

For that 10 Questions I need to show the 3 sub category which is FIXED (EXPRESS , MIDLEVEL, HIGHER).

How to write a elastic search query to get only 10 questions along with the 3 sub category

The scripted field can also return null if the current document is not relevant. Then it won't show up if you use it in a terms aggregation

Would you mind sharing one example for scripted field that to put static 10 questions

I tried this below query: but not working

if (doc['question'].value == 'I need to get parcel as soon as possible','Parcel required as soon as possible') AND Category == 'Expediate'{
return 'Express';
}else if (doc['question'].value == 'I need parcel after 2 days','Parcel required after 2 days') AND Category ==' Medium' {
return 'MIDLEVEL';
}else if (doc['question'].value == 'I need today itself','parcel required today at any cost') AND Category ==' HIGHER' {
return 'MIDLEVEL';
}

Dear Team, Can some one help me with this. Facing challenges

What exactly isn't working? You can use "Get help with the syntax and preview the results of your script." link to get a preview for 10 documents and the return value. This can be helpful to debug your script whether it's doing the right thing.

I Ran below statement:

if (doc['question.keyword'].value == 'I need to get parcel as soon as possible','Parcel required as soon as possible') AND Category == 'Expediate'{
return 'Express';
}else if (doc['question.keyword'].value == 'I need parcel after 2 days','Parcel required after 2 days') AND Category ==' Medium' {
return 'MIDLEVEL';
}else if (doc['question.keyword'].value == 'I need today itself','parcel required today at any cost') AND Category ==' HIGHER' {
return 'MIDLEVEL';
}

I am getting the following error. Do no how to write correct query format.

Your syntax is pretty off - if you have multiple conditions, list them like this:

if ((doc['question.keyword'].value == 'I need to get parcel as soon as possible' || doc['question.keyword'].value == 'Parcel required as soon as possible') && doc['category.keyword'].value == 'Expediate') { //...