Create table from nested array of json objects that includes lookup value

[tomporterfield]

I have an event that looks as follows:

{
    "app_name": "my_app",
    "audit_details": {
        "audit": {
            "responseContentLength": "-1",
            "name": "app_name",
            "details": {
                "detail": [{
                        "messageId": "-4",
                        "time": "1752065281146",
                        "ordinal": "0"
                    }, {
                        "messageId": "7103",
                        "time": "1752065281146",
                        "ordinal": "1"
                    }, {
                        "messageId": "7101",
                        "time": "1752065281146",
                        "ordinal": "2"
                    }
                ]
            }
        }
    }
}

I want to create a table that includes a row for each detail record that includes the messageId, time and ordinal, but also a messageIdDescription that is retrieved from a lookup similar to as follows:

lookup Table_MessageId message_Id as messageId OUTPUT definition as messageIdDescription

the Table_MessageId has three columns - message_Id, definition, audit_Level

Any pointers are appreciated.

[yuanliu · Accepted solution]

Use spath and mvexpand

| spath path=audit_details.audit.details.detail{}
| mvexpand audit_details.audit.details.detail{}
| spath input=audit_details.audit.details.detail{}
| fields - audit_details.audit.details.detail{}*

Your would give

app_name	audit_details.audit.name	audit_details.audit.responseContentLength	messageId	ordinal	time
my_app	app_name	-1	-4	0	1752065281146
my_app	app_name	-1	7103	1	1752065281146
my_app	app_name	-1	7101	2	1752065281146

Here is an emulation for you to play with and compare with real data

| makeresults
| fields - _time
| eval _raw = "{
    \"app_name\": \"my_app\",
    \"audit_details\": {
        \"audit\": {
            \"responseContentLength\": \"-1\",
            \"name\": \"app_name\",
            \"details\": {
                \"detail\": [{
                        \"messageId\": \"-4\",
                        \"time\": \"1752065281146\",
                        \"ordinal\": \"0\"
                    }, {
                        \"messageId\": \"7103\",
                        \"time\": \"1752065281146\",
                        \"ordinal\": \"1\"
                    }, {
                        \"messageId\": \"7101\",
                        \"time\": \"1752065281146\",
                        \"ordinal\": \"2\"
                    }
                ]
            }
        }
    }

}"
| spath
``` data emulation above ```

[tomporterfield]

Thanks, let me give that a go in the overall solution, but it looks very promising.

[tomporterfield]

I was able to successfully get this working with the guidance, thanks.