How to retrieve data form keys value?

[prashantsagar73]

i have a table who contain multiple keys and value one of them keys{"body"} value are below:

"body": "{\n \"Type\" : \"Notification\",\n \"MessageId\" : \"f33b9756-bc6b-5efc-8111-cca792b8d4f3\",\n \"TopicArn\" : \"arn:aws:sns:eu-central-1:108770896200:PL-PRD-notification-media\",\n \"Message\" : \"{\\\"licenseValidFrom\\\":\\\"2022-11-18T07:56:18.760+01:00\\\",\\\"licenseValidUntil\\\": \\\"3022-03-21T07:56:18.760+01:00\\\",\\\"hasCopyright\\\":\\\"False\\\",\\\"resolutionInPx\\\": \\\"685x1664\\\",\\\"resolutionKey\\\":\\\"ORIGINAL\\\",\\\"checksum\\\":\\\"35a63f43ec3088c9cf01b6c5473f1436\\\", \\\"description\\\": \\\"Jewelry Full\\\", \\\"brand\\\": \\\"\\\", \\\"category\\\": \\\"\\\", \\\"mediaType\\\": \\\"AdditionalImage\\\", \\\"status\\\": \\\"Media.Active.490.Finished\\\", \\\"gtin\\\": \\\"9009656409602\\\", \\\"channel\\\": \\\"gkkDigitalDataManagement\\\", \\\"mediaId\\\": \\\"06\\\", \\\"contentType\\\": \\\"image/jpeg\\\"}\",\n \"Timestamp\" : \"2022-11-18T06:56:19.980Z\",\n \"SignatureVersion\" : \"1\",\n \"Signature\" : \"AySfxHK6Y3ZSA7BsgR7sFHva82snBuenk74ZMJ5HzewU4ozOg8PDOnjeBAY0FLbFxomWOEVIzNWp9yW8Ti9lWWNpdzeMd4MYUhN/a0tLwce1Dk0xdAlsM9DByiJHUTWj1QkvUsaJChMaDfZOyFwZNhvHBbtC9W/Y9AtcZnS9ahz8bQBvxIZv/Xb7tK/g0pvOJ2Nx633TN1UStYshQef8g1cV+q4Ey0fMRr9l/K00POuBUCcGZRRXTiGaqVOTWk08ARFsW5a9Iz28kaBz4PDFNdCALgnwdZ65m6k2HL8fYW5O7gvxEqAOLnYcPsX8XLiV20tSd2NBgoytq5f3IxAbsw==\",\n \"MessageAttributes\" : {\n \"channel\" : {\"Type\":\"String\",\"Value\":\"gkkDigitalDataManagement\"},\n \"mediaStatus\" : {\"Type\":\"String\",\"Value\":\"Media.Active.490.Finished\"},\n \"mediaType\" : {\"Type\":\"String\",\"Value\":\"AdditionalImage\"}\n }\n}",

want to retrieve [gtin: 9009656409602] in a separate table

[ITWhisperer]

Since you have escaped embedded JSON, you could try extracting in stages.

| spath
| spath input=body
| spath input=Message

[prashantsagar73]

not working.

my query is : table cxlBusinessData.data.body| spath | spath input=body | spath input=Message

[ITWhisperer]

Sounds like your raw event is not pure JSON - please can you share an anonymised version of your raw events in a code block </>

[prashantsagar73]

the previous result was belong from this data body

[ITWhisperer]

Which field is this data extracted to - for example if it was called body

| spath input=body
| spath input=cxlBusinessData.data.body
| spath input=Message

[prashantsagar73]

it gives u the same output like before one if u call body.

my code :

| table cxlBusinessData.data.body

this one gives me the body part(output) only that i attached with the question, but from that body i want data{ gtin} in a separate table. so i can use it further .

[yuanliu · Accepted solution]

You need to remove spurious characters first.

| eval "cxlBusinessData.data.body" = replace('c', "\\\n ", "")
| spath input=cxlBusinessData.data.body
| spath input=Message
| table gtin