Value in Pivot Field of a transform not found in source index

[sanjaysubramanian]

Hi all

My transform JSON is illustrated below. A screen shot of the Transform Preview is also attached.

TRANSFORM INDEX
On the Kibana Discovery Console (index = interactions_new_conversations_transform_idx) I can search for the following and get results
data.chat : 1699894732081160

ORIGINAL INDEX
However if I search the above ID in the original index (index = interactions), I don't see any results ! I also queried ElasticSearch but found no results !

Thanks
warmly

sanjay

{
  "id": "interactions_new_conversations_transform",
  "source": {
    "index": [
      "interactions"
    ],
    "query": {
      "bool": {
        "should": [
          {
            "match_phrase": {
              "data.sendertype": "customer"
            }
          }
        ],
        "minimum_should_match": 1
      }
    }
  },
  "dest": {
    "index": "interactions_new_conversations_transform_idx"
  },
  "sync": {
    "time": {
      "field": "data.ts",
      "delay": "60s"
    }
  },
  "pivot": {
    "group_by": {
      "data.chat": {
        "histogram": {
          "field": "data.chat",
          "interval": "10"
        }
      }
    },
    "aggregations": {
      "data.ts.min": {
        "min": {
          "field": "data.ts"
        }
      }
    }
  },
  "description": "New Conversations (Chat IDs) from interactions",
  "version": "7.7.0",
  "create_time": 1592435815714
}

[Hendrik_Muhs]

You are using a histogram aggregation as group_by, this builds buckets in ranges of 10:

[0,10)
[10, 20)
...

I guess you want to use a terms aggregation instead, your chat id is a "number" but not "numeric".

[sanjaysubramanian]

Thanks @Hendrik_Muhs
I performed two tests after I wrote this message. Would request your feedback ! Thanks

TEST 1
Change the "data.chat" field data type to "keyword" instead of "long".
This solved the issue but now I lose the "long" property of the "data.chat"

TEST 2
I modified the mapping to look like this
I am trying something else too...I am modding the mapping like this
This way now I have the data type as integer and I also can use the data.chat.kwrd as an aggregation field

                  "type": "long",
                  "fields": {
                     "kwrd": {
                        "type":  "keyword"
                     },
                     "txt": {
                        "type":  "text"
                     }
                  }
                },```

[Hendrik_Muhs]

Have you tried the suggested terms aggregation?

    "group_by": {
      "data.chat": {
        "terms": {
          "field": "data.chat"
        }
      }

Terms does not require a keyword field, but works for long fields. For every group_by transform takes the mapping of the source index as mapping for the destination index.

There is no need to change data.chat to keyword.

[sanjaysubramanian]

Thanks @Hendrik_Muhs, I had not considered manual editing of the pivot specifications on the Kibana Web Console

Through the Kibana Transforms Web UI we get only one option as shown below:

========
{
  "group_by": {
    "data.chat": {
      "histogram": {
        "field": "data.chat",
        "interval": "10"
      }
    }
  },
  "aggregations": {
    "data.ts.min": {
      "min": {
        "field": "data.ts"
      }
    }
  }
}


MODIFIED THROUGH Advanced pivot editor
======================================
{
    "group_by": {
      "data.chat": {
        "terms": {             <<<<<<<<<<<<< I changed this per your recommendation now :-)
          "field": "data.chat"
        }
      }
    },
    "aggregations": {
      "data.ts.min": {
        "min": {
          "field": "data.ts"
        }
      }
    }
  }

[Hendrik_Muhs]

Ah, I see. I will let our UI developers know.

[sanjaysubramanian]

Thank you @Hendrik_Muhs