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?
Is this what you are looking for?
ListValues | ListValues.ListValue._value | ListValues.ListValue.displayName | ListValues.ListValue.id | Record.contentId | Record.levelGuid | Record.levelId | Record.moduleId | Record.parentId | Reference._value | Reference.id | Users.User._value | Users.User.firstName | Users.User.id | Users.User.lastName | Users.User.middleName | guid | id | p | table.border | table.cellpadding | table.cellspacing | table.style | table.tbody.tr.style | table.tbody.tr.td.p | table.tbody.tr.td.style | tbody.tr.td.valign | type | value | xmlConvertedValue |
... | ... | ||||||||||||||||||||||||||||
needs 22981 as field name | Critical | 74550 | 429636 | 3c5b481a-6698-49f5-8111-e43bb7604486 | 57 | 83 | 0 | 8f8df6e3-8fb8-478b-8aa0-0be02bec24e3 | 22981 | 4 | |||||||||||||||||||
429636 | 3c5b481a-6698-49f5-8111-e43bb7604486 | 57 | 83 | 0 | needs 22876 as field name | 256681 | 4cc725ad-d78d-4fc0-a3b2-c2805da8f29a | 22876 | 9 | ||||||||||||||||||||
429636 | 3c5b481a-6698-49f5-8111-e43bb7604486 | 57 | 83 | 0 | needs 23445 as field name | 255761 | f4f262f7-290a-4ffc-af2b-dcccde673dba | 23445 | 9 | ||||||||||||||||||||
429636 | 3c5b481a-6698-49f5-8111-e43bb7604486 | 57 | 83 | 0 | ea8f9a24-3d35-49f9-b74e-e3b9e48f8b3b | 1675 | 2 | ||||||||||||||||||||||
429636 | 3c5b481a-6698-49f5-8111-e43bb7604486 | 57 | 83 | 0 | e563eb9e-6390-406a-ac79-386e1c3006a3 | 22812 | 2 | needs 22812 as field name | |||||||||||||||||||||
429636 | 3c5b481a-6698-49f5-8111-e43bb7604486 | 57 | 83 | 0 | needs 22863 as field name | data | 5117 | data | data | a9fe7505-5877-4bdf-aa28-9f6c86af90ae | 22863 | 8 | |||||||||||||||||
429636 | 3c5b481a-6698-49f5-8111-e43bb7604486 | 57 | 83 | 0 | 4466fd31-3ab3-4117-8aa0-40f765d20c10 | 22784 | 3 | 7/18/2023 | 2023-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 ```
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?