CODEWITHSUNDEEP
splunksplunk-query
Ashish Gupta
Ashish Gupta

Reputation: 15139

Splunk : Extracting the elements from JSON structure as separate fields

In Splunk, I'm trying to extract the key value pairs inside that "tags" element of the JSON structure so each one of the become a separate column so I can search through them. for example :

| spath data | rename data.tags.EmailAddress AS Email

This does not help though and Email field comes as empty.I'm trying to do this for all the tags. Any thoughts/pointers?

{
    "timestamp": "2021-10-26T18:23:05.180707Z",
    "data": {
        "tags": [
            {
                "key": "Email",
                "value": "[email protected]"
            },
            {
                "key": "ProjectCode",
                "value": "ABCD"
            },
            {
                "key": "Owner",
                "value": "John Doe"
            }
        ]
    },
    "field1": "random1",
    "field2": "random2"
}

Upvotes: 5

Views: 19490

Answers (1)

Jerry Jeremiah
Jerry Jeremiah

Reputation: 9618

I think does what you want:

 | spath data.tags{}
 | mvexpand data.tags{}
 | spath input=data.tags{}
 | table key value
 | transpose header_field=key
 | fields - column

How it works:

| spath data.tags{} takes the json and creates a multi value field that contains each item in the tags array

| mvexpand data.tags{} splits the multi value field into individual events - each one contains one of the items in the tags array

| spath input=data.tags{} takes the json in each event and makes a field for each KVP in that item (key and value in this case)

| table key value limits further commands to these two fields

| transpose header_field=key makes a field for each value of the key field (including one for the field named column)`

| fields - column removes the column field from the output

Here is a fully runnable example:

| makeresults
| eval _raw="
{
  \"timestamp\": \"2021-10-26T18:23:05.180707Z\",
  \"data\": {
    \"tags\": [
      {\"key\": \"Email\", \"value\": \"[email protected]\"},
      {\"key\": \"ProjectCode\", \"value\": \"ABCD\"},
      {\"key\": \"Owner\", \"value\": \"John Doe\"}
    ]
  },
  \"field1\": \"random1\",
  \"field2\": \"random2\"
}
"
| spath data.tags{}
| mvexpand data.tags{}
| spath input=data.tags{}
| table key value
| transpose header_field=key

It creates this output:

+----------------------+-------------+----------+
| Email                | ProjectCode | Owner    |
+----------------------+-------------+----------+
| [email protected] | ABCD        | John Doe |
+----------------------+-------------+----------+

Upvotes: 12

Related Questions