How to extract JSON array contents to lookup table?

[AlanMoen]

I've got a JSON array I ingest that I want to extract certain fields from to save into a lookup table.

Here's an example of the JSON:

  {
	"Response": {
		"results": [
			{
				"memberType": 2,
				"isOnline": false,
				"lastOnlineStatusChange": "1657176499",
				"groupId": "1234567",
				"destinyUserInfo": {
					"LastSeenDisplayName": "UserName1",
					"LastSeenDisplayNameType": 1,
					"iconPath": "/img/theme/bungienet/icons/xboxLiveLogo.png",
					"crossSaveOverride": 1,
					"applicableMembershipTypes": [
						2,
						1
					],
					"isPublic": false,
					"membershipType": 1,
					"membershipId": "1234567890123456789",
					"displayName": "UserName1",
					"bungieGlobalDisplayName": "UserName1",
					"bungieGlobalDisplayNameCode": 9999
				},
				"bungieNetUserInfo": {
					"supplementalDisplayName": "UserName1#9999",
					"iconPath": "/img/profile/avatars/default_avatar.gif",
					"crossSaveOverride": 0,
					"isPublic": false,
					"membershipType": 254,
					"membershipId": "12345678",
					"displayName": "UserName1",
					"bungieGlobalDisplayName": "UserName1",
					"bungieGlobalDisplayNameCode": 9999
				},
				"joinDate": "2021-10-27T20:56:48Z"
			},
			{
				"memberType": 2,
				"isOnline": false,
				"lastOnlineStatusChange": "1657390180",
				"groupId": "1234567",
				"destinyUserInfo": {
					"LastSeenDisplayName": "UserName2",
					"LastSeenDisplayNameType": 1,
					"iconPath": "/img/theme/bungienet/icons/xboxLiveLogo.png",
					"crossSaveOverride": 1,
					"applicableMembershipTypes": [
						2,
						3,
						1
					],
					"isPublic": false,
					"membershipType": 1,
					"membershipId": "4611686018431599324",
					"displayName": "UserName2",
					"bungieGlobalDisplayName": "UserName2",
					"bungieGlobalDisplayNameCode": 8888
				},
				"bungieNetUserInfo": {
					"supplementalDisplayName": "UserName2#8888",
					"iconPath": "/img/profile/avatars/HaloRingcopy.gif",
					"crossSaveOverride": 0,
					"isPublic": false,
					"membershipType": 254,
					"membershipId": "1990219",
					"displayName": "UserName2",
					"bungieGlobalDisplayName": "UserName2",
					"bungieGlobalDisplayNameCode": 8888
				},
				"joinDate": "2020-04-07T15:07:21Z"
			}
		],
		"totalResults": 2,
		"hasMore": true,
		"query": {
			"itemsPerPage": 2,
			"currentPage": 1
		},
		"useTotalResults": true
	},
	"ErrorCode": 1,
	"ThrottleSeconds": 0,
	"ErrorStatus": "Success",
	"Message": "Ok",
	"MessageData": {}
}

(I truncated the results array to 2, there are normally many more)
I want to write to a lookup table like this:

_time               | membershipId        | joinDate
2022-07-17 16:20:28 | 1234567890123456789 | 2021-10-27T20:56:48Z
2022-07-17 16:20:28 | 9876543210123456789 | 2020-04-07T15:07:21Z

I can get something close into a table with:

index=myindex 
| rename Response.results{}.destinyUserInfo.membershipId as membershipId Response.results{}.joinDate as JoinDate
| table _time  ID JoinDate

but saving that to a lookup table makes the membershipId and joinDate into multivalue fields and stores all of the values accordingly. I need them separate.

Help?

[yuanliu]

You need to mvexpand on Response.results{} first using 

| spath path=Response.results{}
| mvexpand Response.results{}
| spath input=Response.results{}

After this, the two columns will be listed like 

destinyUserInfo.membershipId	joinDate
1234567890123456789	2021-10-27T20:56:48Z
4611686018431599324	2020-04-07T15:07:21Z

[AlanMoen]

OK -
That does get the ID and joinDate in a table as discrete value pairs, so I'm closer, but trying to add the _time value only associates it with the first value pair.

But it's a start, thanks

[yuanliu]

When you say "only associate time with first value", do you mean you want to discard the rest of values?  If so, no need for mvexpand.   This becomes a much cheaper operation with mvindex.

| spath path=Response.results{}
| eval FirstResult=mvindex('Response.results{}', 0)
| spath input=FirstResult

[AlanMoen]

No - it looks like this when I add _time:

_time               | membershipId       | joinDate
---------------------------------------------------------------
2022/07/18 07:25:00 | 012345678912345678 | 02-21-2020T09:25:36Z
                    | 987654321098765432 | 09-15-2019T17:53:25Z
                    | etc

I need all three fields populated in the lookup field. The _time field is important to some later processes. If a member leaves, I need to know the last time they were found to be there.

[yuanliu]

In that case, using mvexpand is right direction.  You only need to include _time in table.  Here is my complete simulation.

| makeresults
``` simulate data ```
| eval data="{
	\"Response\": {
		\"results\": [
			{
				\"memberType\": 2,
				\"isOnline\": false,
				\"lastOnlineStatusChange\": \"1657176499\",
				\"groupId\": \"1234567\",
				\"destinyUserInfo\": {
					\"LastSeenDisplayName\": \"UserName1\",
					\"LastSeenDisplayNameType\": 1,
					\"iconPath\": \"/img/theme/bungienet/icons/xboxLiveLogo.png\",
					\"crossSaveOverride\": 1,
					\"applicableMembershipTypes\": [
						2,
						1
					],
					\"isPublic\": false,
					\"membershipType\": 1,
					\"membershipId\": \"1234567890123456789\",
					\"displayName\": \"UserName1\",
					\"bungieGlobalDisplayName\": \"UserName1\",
					\"bungieGlobalDisplayNameCode\": 9999
				},
				\"bungieNetUserInfo\": {
					\"supplementalDisplayName\": \"UserName1#9999\",
					\"iconPath\": \"/img/profile/avatars/default_avatar.gif\",
					\"crossSaveOverride\": 0,
					\"isPublic\": false,
					\"membershipType\": 254,
					\"membershipId\": \"12345678\",
					\"displayName\": \"UserName1\",
					\"bungieGlobalDisplayName\": \"UserName1\",
					\"bungieGlobalDisplayNameCode\": 9999
				},
				\"joinDate\": \"2021-10-27T20:56:48Z\"
			},
			{
				\"memberType\": 2,
				\"isOnline\": false,
				\"lastOnlineStatusChange\": \"1657390180\",
				\"groupId\": \"1234567\",
				\"destinyUserInfo\": {
					\"LastSeenDisplayName\": \"UserName2\",
					\"LastSeenDisplayNameType\": 1,
					\"iconPath\": \"/img/theme/bungienet/icons/xboxLiveLogo.png\",
					\"crossSaveOverride\": 1,
					\"applicableMembershipTypes\": [
						2,
						3,
						1
					],
					\"isPublic\": false,
					\"membershipType\": 1,
					\"membershipId\": \"4611686018431599324\",
					\"displayName\": \"UserName2\",
					\"bungieGlobalDisplayName\": \"UserName2\",
					\"bungieGlobalDisplayNameCode\": 8888
				},
				\"bungieNetUserInfo\": {
					\"supplementalDisplayName\": \"UserName2#8888\",
					\"iconPath\": \"/img/profile/avatars/HaloRingcopy.gif\",
					\"crossSaveOverride\": 0,
					\"isPublic\": false,
					\"membershipType\": 254,
					\"membershipId\": \"1990219\",
					\"displayName\": \"UserName2\",
					\"bungieGlobalDisplayName\": \"UserName2\",
					\"bungieGlobalDisplayNameCode\": 8888
				},
				\"joinDate\": \"2020-04-07T15:07:21Z\"
			}
		],
		\"totalResults\": 2,
		\"hasMore\": true,
		\"query\": {
			\"itemsPerPage\": 2,
			\"currentPage\": 1
		},
		\"useTotalResults\": true
	},
	\"ErrorCode\": 1,
	\"ThrottleSeconds\": 0,
	\"ErrorStatus\": \"Success\",
	\"Message\": \"Ok\",
	\"MessageData\": {}
}"
``` data simulation complete ```
| spath input=data path=Response.results{} ``` input= is needed unless _raw is JSON ```
| mvexpand Response.results{}
| spath input=Response.results{}
| table _time destinyUserInfo.membershipId joinDate

_time	estinyUserInfo.membershipId	joinDate
2022-07-18 09:11:49	1234567890123456789	2021-10-27T20:56:48Z
2022-07-18 09:11:49	4611686018431599324	2020-04-07T15:07:21Z

Is this something you are looking for?

[AlanMoen]

Perfect!

Thank you very much for your assistance