Splunk Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question

Need help with nested json and spath

manderson7
Contributor
‎11-16-2023 01:47 PM

I've seen a few of the spath topics around, but wasn't able to understand enough to make it work for my data. I have the following json:

{
  "Record": {
    "contentId": "429636",
    "levelId": "57",
    "levelGuid": "3c5b481a-6698-49f5-8111-e43bb7604486",
    "moduleId": "83",
    "parentId": "0",
    "Field": [
      {
        "id": "22811",
        "guid": "6c6bbe96-deab-46ab-b83b-461364a204e0",
        "type": "1",
        "_value": "Need This with 22811 as the field name"
      },
      {
        "id": "22810",
        "guid": "08f66941-8f2f-42ce-87ae-7bec95bb5d3b",
        "type": "1",
        "p": "need this with 22810 as the field name"
      },
      {
        "id": "478",
        "guid": "4e17baea-f624-4d1a-9c8c-83dd18448689",
        "type": "1",
        "p": [
          "Needs to have 478 as field name",
          "Needs to have 478 as field name"
        ]
      },
      {
        "id": "22859",
        "guid": "f45d3578-100e-44aa-b3d3-1526aa080742",
        "type": "3",
        "xmlConvertedValue": "2023-06-16T00:00:00Z",
        "_value": "needs 22859 as field name"
      },
      {
        "id": "482",
        "guid": "a7ae0730-508b-4545-8cdc-fb68fc2e985a",
        "type": "3",
        "xmlConvertedValue": "2023-08-22T00:00:00Z",
        "_value": "needs 482 as field name"
      },
      {
        "id": "22791",
        "guid": "89fb3582-c325-4bc9-812e-0d25e319bc52",
        "type": "4",
        "ListValues": {
          "ListValue": {
            "id": "74192",
            "displayName": "Exception Closed",
            "_value": "needs 22791 as field name"
          }
        }
      },
      {
        "id": "22818",
        "guid": "e2388e72-cace-42e6-9364-4f936df1b7f4",
        "type": "4",
        "ListValues": {
          "ListValue": {
            "id": "74414",
            "displayName": "Yes",
            "_value": "needs 22818 as field name"
          }
        }
      },
      {
        "id": "22981",
        "guid": "8f8df6e3-8fb8-478b-8aa0-0be02bec24e3",
        "type": "4",
        "ListValues": {
          "ListValue": {
            "id": "74550",
            "displayName": "Critical",
            "_value": "needs 22981 as field name"
          }
        }
      },
      {
        "id": "22876",
        "guid": "4cc725ad-d78d-4fc0-a3b2-c2805da8f29a",
        "type": "9",
        "Reference": {
          "id": "256681",
          "_value": "needs 22876 as field name"
        }
      },
      {
        "id": "23445",
        "guid": "f4f262f7-290a-4ffc-af2b-dcccde673dba",
        "type": "9",
        "Reference": {
          "id": "255761",
          "_value": "needs 23445 as field name"
        }
      },
      {
        "id": "1675",
        "guid": "ea8f9a24-3d35-49f9-b74e-e3b9e48f8b3b",
        "type": "2"
      },
      {
        "id": "22812",
        "guid": "e563eb9e-6390-406a-ac79-386e1c3006a3",
        "type": "2",
        "_value": "needs 22812 as field name"
      },
      {
        "id": "22863",
        "guid": "a9fe7505-5877-4bdf-aa28-9f6c86af90ae",
        "type": "8",
        "Users": {
          "User": {
            "id": "5117",
            "firstName": "data",
            "middleName": "data",
            "lastName": "data",
            "_value": "needs 22863 as field name"
          }
        }
      },
      {
        "id": "22784",
        "guid": "4466fd31-3ab3-4117-8aa0-40f765d20c10",
        "type": "3",
        "xmlConvertedValue": "2023-07-18T00:00:00Z",
        "_value": "7/18/2023"
      },
      {
        "id": "22786",
        "guid": "d1c7af3e-a350-4e59-9353-132a04a73641",
        "type": "1"
      },
      {
        "id": "2808",
        "guid": "4392ae76-9ee1-45bf-ac31-9e323a518622",
        "type": "1",
        "p": "needs 2808 as field name"
      },
      {
        "id": "22802",
        "guid": "ad7d4268-e386-441d-90b1-2da2fba0d002",
        "type": "1",
        "table": {
          "style": "width: 954px",
          "border": "1",
          "cellspacing": "0",
          "cellpadding": "0",
          "tbody": {
            "tr": {
              "style": "height: 73.05pt",
              "td": {
                "style": "width: 715.5pt",
                "valign": "top",
                "p": "needs 22802 as field name"
              }
            }
          }
        }
      },
      {
        "id": "8031",
        "guid": "fbcfdf2c-2990-41d1-9139-8a1d255688b0",
        "type": "1",
        "table": {
          "style": "width: 954px",
          "border": "1",
          "cellspacing": "0",
          "cellpadding": "0",
          "tbody": {
            "tr": {
              "style": "height: 71.1pt",
              "td": {
                "style": "width: 715.5pt",
                "valign": "top",
                "p": [
                  "needs 8031 as field name",
                  "needs 8031 as field name"
                ]
              }
            }
          }
        }
      },
      {
        "id": "22820",
        "guid": "0f98830d-48b3-497c-b965-55be276037f2",
        "type": "1",
        "p": "needs 22820 as field name"
      },
      {
        "id": "22807",
        "guid": "8aa0d0fa-632d-4dfa-9867-b0cc407fa96b",
        "type": "3"
      },
      {
        "id": "22855",
        "guid": "e55cbc59-ad8d-4831-8e6f-d350046026e9",
        "type": "1"
      },
      {
        "id": "8032",
        "guid": "f916365b-e6eb-4ab9-a4ff-c7812a404854",
        "type": "1",
        "p": "needs 8032 as field name"
      },
      {
        "id": "22792",
        "guid": "8e70c28a-2eec-4e38-b78b-5495c2854b3e",
        "type": "1",
        "_value": "needs 22792 as field name "
      },
      {
        "id": 22793,
        "guid": "ffeaa385-643a-4f04-8a00-c28ddd026b7f",
        "type": "4",
        "ListValues": ""
      },
      {
        "id": "22795",
        "guid": "c46eac60-d86e-4af4-9292-d194a601f8b6",
        "type": "1"
      },
      {
        "id": "22797",
        "guid": "8cd6e398-e565-4034-8db8-2e2ecb2f0b31",
        "type": "4",
        "ListValues": {
          "ListValue": {
            "id": "73060",
            "displayName": "data",
            "_value": "needs 22797 as field name"
          }
        }
      },
      {
        "id": "22799",
        "guid": "20823b18-cb9b-47a3-854d-58f874164b27",
        "type": "4",
        "ListValues": {
          "ListValue": {
            "id": "74410",
            "displayName": "Other",
            "_value": "needs 22799 as field name"
          }
        }
      },
      {
        "id": "22798",
        "guid": "5b32be4c-bc40-45b3-add4-1b22162fd882",
        "type": "4",
        "ListValues": {
          "ListValue": {
            "id": "74405",
            "displayName": "N/A",
            "_value": "needs 22798 as field name"
          }
        }
      },
      {
        "id": "22800",
        "guid": "6b020db0-780f-4eaf-8381-c122425b71ed",
        "type": "1",
        "p": "needs 22800 as field name"
      },
      {
        "id": "22801",
        "guid": "06334da8-5392-4a9d-a3eb-d4075ee30787",
        "type": "1",
        "p": "needs 22801 as field name"
      },
      {
        "id": "22794",
        "guid": "25da1de8-8e81-4281-8ef3-d82d1dc005ad",
        "type": "4",
        "ListValues": {
          "ListValue": {
            "id": "74398",
            "displayName": "Yes",
            "_value": "needs 22794 as field name"
          }
        }
      },
      {
        "id": "22813",
        "guid": "89760b4f-49be-40ad-8429-89c247e3e95a",
        "type": "1",
        "p": "needs 22813 as field name"
      },
      {
        "id": "22803",
        "guid": "03b6c826-e15c-4356-89e8-b0bd509aaeb5",
        "type": "3",
        "xmlConvertedValue": "2023-06-15T00:00:00Z",
        "_value": "needs 22803 as field name"
      },
      {
        "id": "22804",
        "guid": "d7683f9c-97bb-461a-97df-36ec6596b4fc",
        "type": "1",
        "p": "needs 22804 as field name"
      },
      {
        "id": "22805",
        "guid": "33386a3a-c331-4d8c-9825-166c0a5235c2",
        "type": "3",
        "xmlConvertedValue": "2023-06-15T00:00:00Z",
        "_value": "needs 22805 as field name"
      },
      {
        "id": "22806",
        "guid": "cd486293-9857-475c-9da3-a06f836edb59",
        "type": "1",
        "p": "needs 22806 as field name"
      }
    ]
  }
}

and have been able to extract id, (some) p data and _value data from Record.Field{} using:

| spath path=Record.Field{} output=Field 
| mvexpand Field 
| spath input=Field 
| rename id AS Field_id, value AS Field_value, p AS Field_p


, but have been unable get any other data out. The p values that I can get out are single value only.
In particular, I need to get the multi-value fields for ListValues{}.ListValue out. In addition, I need to map the values in _value and p to the top ID field in that array. I think the code sample provided above explains what's needed.
I know I can do a

|eval {id}=value


but it's complicated when there are so many more fields other than value, or complicated when the fields are nested.

Can someone help with this?

Labels (2)
Labels
Tags (3)
0 Karma
Reply

yuanliu
SplunkTrust
SplunkTrust
‎11-16-2023 08:20 PM

Is this what you are looking for?

ListValuesListValues.ListValue._valueListValues.ListValue.displayNameListValues.ListValue.idRecord.contentIdRecord.levelGuidRecord.levelIdRecord.moduleIdRecord.parentIdReference._valueReference.idUsers.User._valueUsers.User.firstNameUsers.User.idUsers.User.lastNameUsers.User.middleNameguididptable.bordertable.cellpaddingtable.cellspacingtable.styletable.tbody.tr.styletable.tbody.tr.td.ptable.tbody.tr.td.styletbody.tr.td.valigntypevaluexmlConvertedValue
......                            
 needs 22981 as field nameCritical745504296363c5b481a-6698-49f5-8111-e43bb760448657830       8f8df6e3-8fb8-478b-8aa0-0be02bec24e322981         4  
    4296363c5b481a-6698-49f5-8111-e43bb760448657830needs 22876 as field name256681     4cc725ad-d78d-4fc0-a3b2-c2805da8f29a22876         9  
    4296363c5b481a-6698-49f5-8111-e43bb760448657830needs 23445 as field name255761     f4f262f7-290a-4ffc-af2b-dcccde673dba23445         9  
    4296363c5b481a-6698-49f5-8111-e43bb760448657830       ea8f9a24-3d35-49f9-b74e-e3b9e48f8b3b1675         2  
    4296363c5b481a-6698-49f5-8111-e43bb760448657830       e563eb9e-6390-406a-ac79-386e1c3006a322812         2needs 22812 as field name 
    4296363c5b481a-6698-49f5-8111-e43bb760448657830  needs 22863 as field namedata5117datadataa9fe7505-5877-4bdf-aa28-9f6c86af90ae22863         8  
    4296363c5b481a-6698-49f5-8111-e43bb760448657830       4466fd31-3ab3-4117-8aa0-40f765d20c1022784         37/18/20232023-07-18T00:00:00Z
......                            

Just repeat the same array flatten method

 

 

 

| fields - Record.Field{}.*
| spath path=Record.Field{}
| mvexpand Record.Field{}
| spath input=Record.Field{}
| mvexpand p{}
| mvexpand table.tbody.tr.td.p{}
| eval p = coalesce(p, 'p{}'), table.tbody.tr.td.p = coalesce(table.tbody.tr.td.p, 'table.tbody.tr.td.p{}')
| fields - Record.Field{} p{} table.tbody.tr.td.p{}

 

 

 

Here's an emulation you can play with and compare with your data

 

 

 

| makeresults
| eval _raw = "{
  \"Record\": {
    \"contentId\": \"429636\",
    \"levelId\": \"57\",
    \"levelGuid\": \"3c5b481a-6698-49f5-8111-e43bb7604486\",
    \"moduleId\": \"83\",
    \"parentId\": \"0\",
    \"Field\": [
      {
        \"id\": \"22811\",
        \"guid\": \"6c6bbe96-deab-46ab-b83b-461364a204e0\",
        \"type\": \"1\",
        \"_value\": \"Need This with 22811 as the field name\"
      },
      {
        \"id\": \"22810\",
        \"guid\": \"08f66941-8f2f-42ce-87ae-7bec95bb5d3b\",
        \"type\": \"1\",
        \"p\": \"need this with 22810 as the field name\"
      },
      {
        \"id\": \"478\",
        \"guid\": \"4e17baea-f624-4d1a-9c8c-83dd18448689\",
        \"type\": \"1\",
        \"p\": [
          \"Needs to have 478 as field name\",
          \"Needs to have 478 as field name\"
        ]
      },
      {
        \"id\": \"22859\",
        \"guid\": \"f45d3578-100e-44aa-b3d3-1526aa080742\",
        \"type\": \"3\",
        \"xmlConvertedValue\": \"2023-06-16T00:00:00Z\",
        \"_value\": \"needs 22859 as field name\"
      },
      {
        \"id\": \"482\",
        \"guid\": \"a7ae0730-508b-4545-8cdc-fb68fc2e985a\",
        \"type\": \"3\",
        \"xmlConvertedValue\": \"2023-08-22T00:00:00Z\",
        \"_value\": \"needs 482 as field name\"
      },
      {
        \"id\": \"22791\",
        \"guid\": \"89fb3582-c325-4bc9-812e-0d25e319bc52\",
        \"type\": \"4\",
        \"ListValues\": {
          \"ListValue\": {
            \"id\": \"74192\",
            \"displayName\": \"Exception Closed\",
            \"_value\": \"needs 22791 as field name\"
          }
        }
      },
      {
        \"id\": \"22818\",
        \"guid\": \"e2388e72-cace-42e6-9364-4f936df1b7f4\",
        \"type\": \"4\",
        \"ListValues\": {
          \"ListValue\": {
            \"id\": \"74414\",
            \"displayName\": \"Yes\",
            \"_value\": \"needs 22818 as field name\"
          }
        }
      },
      {
        \"id\": \"22981\",
        \"guid\": \"8f8df6e3-8fb8-478b-8aa0-0be02bec24e3\",
        \"type\": \"4\",
        \"ListValues\": {
          \"ListValue\": {
            \"id\": \"74550\",
            \"displayName\": \"Critical\",
            \"_value\": \"needs 22981 as field name\"
          }
        }
      },
      {
        \"id\": \"22876\",
        \"guid\": \"4cc725ad-d78d-4fc0-a3b2-c2805da8f29a\",
        \"type\": \"9\",
        \"Reference\": {
          \"id\": \"256681\",
          \"_value\": \"needs 22876 as field name\"
        }
      },
      {
        \"id\": \"23445\",
        \"guid\": \"f4f262f7-290a-4ffc-af2b-dcccde673dba\",
        \"type\": \"9\",
        \"Reference\": {
          \"id\": \"255761\",
          \"_value\": \"needs 23445 as field name\"
        }
      },
      {
        \"id\": \"1675\",
        \"guid\": \"ea8f9a24-3d35-49f9-b74e-e3b9e48f8b3b\",
        \"type\": \"2\"
      },
      {
        \"id\": \"22812\",
        \"guid\": \"e563eb9e-6390-406a-ac79-386e1c3006a3\",
        \"type\": \"2\",
        \"_value\": \"needs 22812 as field name\"
      },
      {
        \"id\": \"22863\",
        \"guid\": \"a9fe7505-5877-4bdf-aa28-9f6c86af90ae\",
        \"type\": \"8\",
        \"Users\": {
          \"User\": {
            \"id\": \"5117\",
            \"firstName\": \"data\",
            \"middleName\": \"data\",
            \"lastName\": \"data\",
            \"_value\": \"needs 22863 as field name\"
          }
        }
      },
      {
        \"id\": \"22784\",
        \"guid\": \"4466fd31-3ab3-4117-8aa0-40f765d20c10\",
        \"type\": \"3\",
        \"xmlConvertedValue\": \"2023-07-18T00:00:00Z\",
        \"_value\": \"7/18/2023\"
      },
      {
        \"id\": \"22786\",
        \"guid\": \"d1c7af3e-a350-4e59-9353-132a04a73641\",
        \"type\": \"1\"
      },
      {
        \"id\": \"2808\",
        \"guid\": \"4392ae76-9ee1-45bf-ac31-9e323a518622\",
        \"type\": \"1\",
        \"p\": \"needs 2808 as field name\"
      },
      {
        \"id\": \"22802\",
        \"guid\": \"ad7d4268-e386-441d-90b1-2da2fba0d002\",
        \"type\": \"1\",
        \"table\": {
          \"style\": \"width: 954px\",
          \"border\": \"1\",
          \"cellspacing\": \"0\",
          \"cellpadding\": \"0\",
          \"tbody\": {
            \"tr\": {
              \"style\": \"height: 73.05pt\",
              \"td\": {
                \"style\": \"width: 715.5pt\",
                \"valign\": \"top\",
                \"p\": \"needs 22802 as field name\"
              }
            }
          }
        }
      },
      {
        \"id\": \"8031\",
        \"guid\": \"fbcfdf2c-2990-41d1-9139-8a1d255688b0\",
        \"type\": \"1\",
        \"table\": {
          \"style\": \"width: 954px\",
          \"border\": \"1\",
          \"cellspacing\": \"0\",
          \"cellpadding\": \"0\",
          \"tbody\": {
            \"tr\": {
              \"style\": \"height: 71.1pt\",
              \"td\": {
                \"style\": \"width: 715.5pt\",
                \"valign\": \"top\",
                \"p\": [
                  \"needs 8031 as field name\",
                  \"needs 8031 as field name\"
                ]
              }
            }
          }
        }
      },
      {
        \"id\": \"22820\",
        \"guid\": \"0f98830d-48b3-497c-b965-55be276037f2\",
        \"type\": \"1\",
        \"p\": \"needs 22820 as field name\"
      },
      {
        \"id\": \"22807\",
        \"guid\": \"8aa0d0fa-632d-4dfa-9867-b0cc407fa96b\",
        \"type\": \"3\"
      },
      {
        \"id\": \"22855\",
        \"guid\": \"e55cbc59-ad8d-4831-8e6f-d350046026e9\",
        \"type\": \"1\"
      },
      {
        \"id\": \"8032\",
        \"guid\": \"f916365b-e6eb-4ab9-a4ff-c7812a404854\",
        \"type\": \"1\",
        \"p\": \"needs 8032 as field name\"
      },
      {
        \"id\": \"22792\",
        \"guid\": \"8e70c28a-2eec-4e38-b78b-5495c2854b3e\",
        \"type\": \"1\",
        \"_value\": \"needs 22792 as field name \"
      },
      {
        \"id\": 22793,
        \"guid\": \"ffeaa385-643a-4f04-8a00-c28ddd026b7f\",
        \"type\": \"4\",
        \"ListValues\": \"\"
      },
      {
        \"id\": \"22795\",
        \"guid\": \"c46eac60-d86e-4af4-9292-d194a601f8b6\",
        \"type\": \"1\"
      },
      {
        \"id\": \"22797\",
        \"guid\": \"8cd6e398-e565-4034-8db8-2e2ecb2f0b31\",
        \"type\": \"4\",
        \"ListValues\": {
          \"ListValue\": {
            \"id\": \"73060\",
            \"displayName\": \"data\",
            \"_value\": \"needs 22797 as field name\"
          }
        }
      },
      {
        \"id\": \"22799\",
        \"guid\": \"20823b18-cb9b-47a3-854d-58f874164b27\",
        \"type\": \"4\",
        \"ListValues\": {
          \"ListValue\": {
            \"id\": \"74410\",
            \"displayName\": \"Other\",
            \"_value\": \"needs 22799 as field name\"
          }
        }
      },
      {
        \"id\": \"22798\",
        \"guid\": \"5b32be4c-bc40-45b3-add4-1b22162fd882\",
        \"type\": \"4\",
        \"ListValues\": {
          \"ListValue\": {
            \"id\": \"74405\",
            \"displayName\": \"N/A\",
            \"_value\": \"needs 22798 as field name\"
          }
        }
      },
      {
        \"id\": \"22800\",
        \"guid\": \"6b020db0-780f-4eaf-8381-c122425b71ed\",
        \"type\": \"1\",
        \"p\": \"needs 22800 as field name\"
      },
      {
        \"id\": \"22801\",
        \"guid\": \"06334da8-5392-4a9d-a3eb-d4075ee30787\",
        \"type\": \"1\",
        \"p\": \"needs 22801 as field name\"
      },
      {
        \"id\": \"22794\",
        \"guid\": \"25da1de8-8e81-4281-8ef3-d82d1dc005ad\",
        \"type\": \"4\",
        \"ListValues\": {
          \"ListValue\": {
            \"id\": \"74398\",
            \"displayName\": \"Yes\",
            \"_value\": \"needs 22794 as field name\"
          }
        }
      },
      {
        \"id\": \"22813\",
        \"guid\": \"89760b4f-49be-40ad-8429-89c247e3e95a\",
        \"type\": \"1\",
        \"p\": \"needs 22813 as field name\"
      },
      {
        \"id\": \"22803\",
        \"guid\": \"03b6c826-e15c-4356-89e8-b0bd509aaeb5\",
        \"type\": \"3\",
        \"xmlConvertedValue\": \"2023-06-15T00:00:00Z\",
        \"_value\": \"needs 22803 as field name\"
      },
      {
        \"id\": \"22804\",
        \"guid\": \"d7683f9c-97bb-461a-97df-36ec6596b4fc\",
        \"type\": \"1\",
        \"p\": \"needs 22804 as field name\"
      },
      {
        \"id\": \"22805\",
        \"guid\": \"33386a3a-c331-4d8c-9825-166c0a5235c2\",
        \"type\": \"3\",
        \"xmlConvertedValue\": \"2023-06-15T00:00:00Z\",
        \"_value\": \"needs 22805 as field name\"
      },
      {
        \"id\": \"22806\",
        \"guid\": \"cd486293-9857-475c-9da3-a06f836edb59\",
        \"type\": \"1\",
        \"p\": \"needs 22806 as field name\"
      }
    ]
  }
}"
| spath
``` emulation above ```

 

 

 

 

 

 

0 Karma
Reply

bowesmana
SplunkTrust
SplunkTrust
‎11-16-2023 04:12 PM

To get p which takes either the single or mv field, use

| eval p_values=coalesce(p, 'p{}')

I don't understand the ListValues.Listvalue part - these are not an array, so only single value fields in your example. Can you give an example of what in your example data should get mapped in the _value and p cases to the top array?

 

0 Karma
Reply
Get Updates on the Splunk Community!

Stay Connected: Your Guide to May Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars this month. This ...

They're back! Join the SplunkTrust and MVP at .conf24

With our highly anticipated annual conference, .conf, comes the fez-wearers you can trust! The SplunkTrust, as ...

Enterprise Security Content Update (ESCU) | New Releases

Last month, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...