# Split json message into a new column in kibana table
**URL:** https://discuss.elastic.co/t/split-json-message-into-a-new-column-in-kibana-table/224565
**Category:** Logstash
**Created:** [March 22, 2020, 3:08pm UTC](https://discuss.elastic.co/t/split-json-message-into-a-new-column-in-kibana-table/224565 "2020-03-22T15:08:18Z")
**Posts on this page:** 14
**Page:** 1
### Author:  [@ARoy](https://discuss.elastic.co/u/ARoy)
#### Post date: [March 22, 2020, 3:08pm UTC](https://discuss.elastic.co/t/split-json-message-into-a-new-column-in-kibana-table/224565/1 "2020-03-22T15:08:19Z")
Hi, I need to add new column in kibana from the json message.
The message is as follows :
{"results":[{"gender":"female","name":{"title":"Ms","first":"Eden","last":"Morel"},"location":{"street":{"number":1412,"name":"Rue Abel-Hovelacque"},"city":"Angers","state":"Pyrénées-Atlantiques","country":"France","postcode":21446,"coordinates":{"latitude":"-3.8242","longitude":"87.5006"},"timezone":{"offset":"-12:00","description":"Eniwetok, Kwajalein"}},"[email":"eden.morel@example.com](mailto:email%22:%22eden.morel@example.com)","login":{"uuid":"4be0250c-2848-4db5-a6a2-c0cf9e6730c3","username":"bluerabbit132","password":"nipper","salt":"FsH1RuQB","md5":"d25604b4f525093a171bf9f9c0f16268","sha1":"76c392c6a13bb191ec219a8a9907e7cb914fcf92","sha256":"8d9f2fbe1e9174d8a42d2cacde36dab5feda235b377f43ec5f3f9022de67ab1f"},"dob":{"date":"1988-04-29T06:59:05.741Z","age":32},"registered":{"date":"2019-09-15T22:27:53.874Z","age":1},"phone":"05-44-20-72-74","cell":"06-83-85-66-62","id":{"name":"INSEE","value":"2NNaN47984362 17"},"picture":{"large":"[https://randomuser.me/api/portraits/women/57.jpg","medium":"https://randomuser.me/api/portraits/med/women/57.jpg","thumbnail":"https://randomuser.me/api/portraits/thumb/women/57.jpg"},"nat":"FR"}],"info":{"seed":"61e2198c825b6740","results":1,"page":1,"version":"1.3](https://randomuser.me/api/portraits/women/57.jpg%22,%22medium%22:%22https://randomuser.me/api/portraits/med/women/57.jpg%22,%22thumbnail%22:%22https://randomuser.me/api/portraits/thumb/women/57.jpg%22%7D,%22nat%22:%22FR%22%7D%5D,%22info%22:%7B%22seed%22:%2261e2198c825b6740%22,%22results%22:1,%22page%22:1,%22version%22:%221.3)"}}
Currently the message filed has all the data, and I need them into separate columns when discovered in kibana. I tried to add a new column in the logstash file as follows but no luck :
filter {
split {
add\_field =\> {
"foo\_%{somefield}" =\> "Hello world, from %{host}"
"new\_field" =\> "new\_static\_value"
}
}
}
I am new to elk, can you please help.
Thanks,
A Roy.
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [March 22, 2020, 4:42pm UTC](https://discuss.elastic.co/t/split-json-message-into-a-new-column-in-kibana-table/224565/2 "2020-03-22T16:42:16Z")
> [@ARoy](#):
>
> split
Try mutate instead if split.
---
### Author:  [@ARoy](https://discuss.elastic.co/u/ARoy)
#### Post date: [March 22, 2020, 5:09pm UTC](https://discuss.elastic.co/t/split-json-message-into-a-new-column-in-kibana-table/224565/3 "2020-03-22T17:09:01Z")
Hi Badger,
I tried to mutate as follows although the applicationID is set, but there is no newId :
filter {
grok {
match =\> { "message" =\> "%{GREEDYDATA:message}"}
}
```
mutate {
split => ["source", "/"]
add_field => { "applicationID" => "11" }
add_field => { "newID" => "%{source[0]}" }
}
}
```
is there any change i need to do in the parse ?
Basically I need to display any field like name, gender location etc from the message field.
Thanks,
A Roy
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [March 22, 2020, 5:13pm UTC](https://discuss.elastic.co/t/split-json-message-into-a-new-column-in-kibana-table/224565/4 "2020-03-22T17:13:07Z")
Have you looked at an event and verified that it has a source field and not, for example, a [log][file][path] field?
---
### Author:  [@ARoy](https://discuss.elastic.co/u/ARoy)
#### Post date: [March 22, 2020, 5:36pm UTC](https://discuss.elastic.co/t/split-json-message-into-a-new-column-in-kibana-table/224565/5 "2020-03-22T17:36:02Z")
Yes, here is the list of available fields in kibana
It did not have a source field so used message instead.
Also i modified the earlier filter as follows but still no new field :
filter {
grok {
match =\> { "message" =\> "%{GREEDYDATA:message}"}
}
```
mutate {
split => ["message", "/"]
add_field => { "applicationID" => "11a" }
add_field => { "newID" => "%{message[0]}" }
}
}
```
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [March 22, 2020, 11:27pm UTC](https://discuss.elastic.co/t/split-json-message-into-a-new-column-in-kibana-table/224565/6 "2020-03-22T23:27:50Z")
OK, so the source field does not exist. Also a grok that consumes the whole of the message field in one GREEDYDATA and assigns it to the message field makes no sense.
Try using a json filter.
```
json { source => "message" }
```
---
### Author:  [@ARoy](https://discuss.elastic.co/u/ARoy)
#### Post date: [March 23, 2020, 7:48am UTC](https://discuss.elastic.co/t/split-json-message-into-a-new-column-in-kibana-table/224565/7 "2020-03-23T07:48:20Z")
My Json conf file is as follows :
input
{
http\_poller {
urls =\> {
urlname =\> "[https://randomuser.me/api](https://randomuser.me/api)"
}
request\_timeout =\> 60
schedule =\> { every =\> "20s"}
codec =\> "json"
}
}
filter
{
json { source =\> "message" }
```
mutate
{
add_field => { "applicationID" => "12" }
add_field => { "newfield" => "[message][results][0]"}
}
```
}
output
{
elasticsearch
{
hosts =\> "[http://localhost:9200](http://localhost:9200)"
index =\> "logstash\_http\_poller\_61"
document\_type =\> "npoller"
}
```
stdout
{
codec => dots
}
```
}
But although i can add applicationID in new columns but newfield is not added.
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [March 23, 2020, 2:51pm UTC](https://discuss.elastic.co/t/split-json-message-into-a-new-column-in-kibana-table/224565/8 "2020-03-23T14:51:45Z")
> [@ARoy](#):
>
> add\_field =\> { "newfield" =\> "[message][results][0]"}
You need to look at your data. Either in Kibana, in the JSON tab on an expanded event in the Discover pane, or else use
```
output { stdout { codec => rubydebug } }
```
I would not expect the field to be called [message][results][0], just [results][0].
---
### Author:  [@ARoy](https://discuss.elastic.co/u/ARoy)
#### Post date: [March 23, 2020, 4:58pm UTC](https://discuss.elastic.co/t/split-json-message-into-a-new-column-in-kibana-table/224565/9 "2020-03-23T16:58:37Z")
Hi, have added filter like this :
```auto
filter {
json { source => "message" }
mutate {
add_field => { "applicationID" => "12" }
add_field => { "newID" => "[results][0]"}
}
}
```
But the debug output as follows does not set for newId still :
```auto
{
"newID" => "[results][0]",
"info" => {
"results" => 1,
"version" => "1.3",
"seed" => "04dd7c27f572a217",
"page" => 1
},
"@version" => "1",
"@timestamp" => 2020-03-23T16:53:40.712Z,
"results" => [
[0] {
"login" => {
"md5" => "8d15f677a7b7ac0b06fe9f5c5e5e5a47",
"sha1" => "f6b850f227785b3040c94493514cfa260400d1d4",
"salt" => "zddTLVFc",
"password" => "virgil",
"uuid" => "706d5358-600b-49c8-8fd9-c60f8a08afa8",
"username" => "ticklishmouse829",
"sha256" => "f4f6842e4241471e95c8a329a42216663ee6200f1093da14e28566fdb20d5d2f"
},
"id" => {
"name" => "",
"value" => nil
},
"location" => {
"country" => "Germany",
"coordinates" => {
"longitude" => "-38.3661",
"latitude" => "-37.7415"
},
"street" => {
"number" => 8714,
"name" => "Gartenweg"
},
"postcode" => 56273,
"state" => "Hamburg",
"timezone" => {
"description" => "Mid-Atlantic",
"offset" => "-2:00"
},
"city" => "Ellwangen (Jagst)"
},
"phone" => "0560-5696538",
"dob" => {
"age" => 53,
"date" => "1967-11-28T07:34:30.260Z"
},
"gender" => "male",
"name" => {
"first" => "Ilhan",
"title" => "Mr",
"last" => "Angerer"
},
"cell" => "0177-1314699",
"registered" => {
"age" => 17,
"date" => "2003-01-19T18:56:32.652Z"
},
"nat" => "DE",
"picture" => {
"thumbnail" => "https://randomuser.me/api/portraits/thumb/men/98.jpg",
"medium" => "https://randomuser.me/api/portraits/med/men/98.jpg",
"large" => "https://randomuser.me/api/portraits/men/98.jpg"
},
"email" => "ilhan.angerer@example.com"
}
],
"applicationID" => "12"
}
```
Following are the results field value in kibana :
```auto
{
"login": {
"uuid": "efb3b528-9346-486d-aa8b-5c1d25f55553",
"salt": "oqAHx3jP",
"md5": "2c5d872419f1fea6371b1966c316610e",
"sha256": "b0969c257980ce2879d3f25094f0c2de66ceb553b29c0a1a73df1f2c1c519471",
"username": "sadfrog808",
"password": "lisalisa",
"sha1": "951a7c417b960db9bf173a9ab7bb9fdfbff92f91"
},
"registered": {
"date": "2017-03-13T03:16:58.686Z",
"age": 3
},
"location": {
"postcode": 2349,
"timezone": {
"description": "Central Time (US & Canada), Mexico City",
"offset": "-6:00"
},
"city": "Bendigo",
"state": "South Australia",
"country": "Australia",
"street": {
"number": 7073,
"name": "Hamilton Ave"
},
"coordinates": {
"latitude": "-86.5667",
"longitude": "95.8325"
}
},
"name": {
"title": "Ms",
"last": "Williams",
"first": "Sue"
},
"nat": "AU",
"id": {
"value": "707048521",
"name": "TFN"
},
"email": "sue.williams@example.com",
"gender": "female",
"dob": {
"date": "1949-04-17T11:37:50.272Z",
"age": 71
},
"phone": "03-9333-3244",
"picture": {
"large": "https://randomuser.me/api/portraits/women/16.jpg",
"thumbnail": "https://randomuser.me/api/portraits/thumb/women/16.jpg",
"medium": "https://randomuser.me/api/portraits/med/women/16.jpg"
},
"cell": "0474-839-219"
}
```
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [March 23, 2020, 7:01pm UTC](https://discuss.elastic.co/t/split-json-message-into-a-new-column-in-kibana-table/224565/10 "2020-03-23T19:01:14Z")
Please edit your post, select the configuration and click on \ in the tool bar above the edit pane. You will see the preview pane on the right change from
filter {
json { source =\> "message" }
mutate {
add\_field =\> { "applicationID" =\> "12" }
add\_field =\> { "newID" =\> "[results][0]"}
}
}
to
```
filter {
json { source => "message" }
mutate {
add_field => { "applicationID" => "12" }
add_field => { "newID" => "[results][0]"}
}
}
```
Then do the same for the blocks of JSON.
---
### Author:  [@ARoy](https://discuss.elastic.co/u/ARoy)
#### Post date: [March 24, 2020, 10:02am UTC](https://discuss.elastic.co/t/split-json-message-into-a-new-column-in-kibana-table/224565/11 "2020-03-24T10:02:55Z")
Hi Badger,
Sorry for the unformulated text earlier, I have reformatted them.
Please let me know where I am going wrong here, since i am not able to add any column/field from the results field in kibana. I need it urgently.
Thanks again.
A Roy.
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [March 24, 2020, 12:42pm UTC](https://discuss.elastic.co/t/split-json-message-into-a-new-column-in-kibana-table/224565/12 "2020-03-24T12:42:39Z")
> [@ARoy](#):
>
> add\_field =\> { "newID" =\> "[results][0]"}
For a sprintf reference you need the %{}. Try
```
add_field => { "newID" => "%{[results][0]}"}
```
---
### Author:  [@ARoy](https://discuss.elastic.co/u/ARoy)
#### Post date: [March 24, 2020, 3:07pm UTC](https://discuss.elastic.co/t/split-json-message-into-a-new-column-in-kibana-table/224565/13 "2020-03-24T15:07:28Z")
Thank you so much @Badger, it is working as expected now.
🙂
Regards,
Abhardeep
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [April 21, 2020, 3:07pm UTC](https://discuss.elastic.co/t/split-json-message-into-a-new-column-in-kibana-table/224565/14 "2020-04-21T15:07:32Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.