Parsing json log to tabular table

Question

I would like to parse the below json to a tablular table as the below.

```
time="2024-05-17T12:40:02Z" level=info msg="{"topLevel":[{"item1":"value1","amount":101,"item2":"hello","ts":"2021-01-21T09:54:33Z","cycle":1,"item3":"test","item4":"a68c1685-0220","additionalData":{"item5":"something","item6":"car","item7":"van"}},{"item1":"value2","amount":102,"item2":"pal","ts":"2021-01-21T02:55:04Z","cycle":2,"item3":"hello","item4":"7570a6e6-c62d","additionalData":{"item5":"car","item6":"red","item7":"blue"}}]}"
```

|item1|amount|item2|ts|cycle|item3|item4|item5|item6|item7|
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
|value1|101|hello|2021-01-21T09:54:33Z|1|test|a68c1685-0220|something|car|van|
|value2|102|pal|2021-01-21T02:55:04Z|2|hello|7570a6e6-c62d|car|red|blue|

Notice that the json is coming with scaped quotes.

I manage to get the first row but can't get to display the second row.

My query...

```
fields @timestamp, @message
| filter @message like '{"topLevel"'
| parse '{\"topLevel\":[{\"item1\":\"*\",\"amount\":*,\"item2\":\"*\",\"ts\":\"*\",\"cycle\":*,\"item3\":\"*\",\"item4\":\"*\",\"additionalData\":*}' as item1, amount, item2, time, cycle, item3, item4, additionalData
| parse additionalData '{"item5":"*","item6":"*","item7":"*"' as item5, item6, item7
| display item1, amount, item2, time, cycle, item3, item4, item5, item6, item7
```

Sagar_T · Answer

Hi there,

Thank you for reaching out to us.

I understand that you are trying to filter and parse the details of your setup. You have a query which is unique in nature, as well as the desired output of how it should be delivered.

Hence to further assist you in the best way possible with this query, we will need to perform a-lot of trial and error in the way  we write the query.

Hence, we require details that are non-public information. Please open a support case with AWS using the following : https://console.aws.amazon.com/support/home#/case/create

By reaching out to us via a case, we can further assist you via screen share and understand your requirement in detail and help you.

Good Day!

item1	amount	item2	ts	cycle	item3	item4	item5	item6	item7
value1	101	hello	2021-01-21T09:54:33Z	1	test	a68c1685-0220	something	car	van
value2	102	pal	2021-01-21T02:55:04Z	2	hello	7570a6e6-c62d	car	red	blue

Parsing json log to tabular table

Relevant content