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
You need to remove spurious characters first.
| eval "cxlBusinessData.data.body" = replace('c', "\\\n ", "") | spath input=cxlBusinessData.data.body | spath input=Message | table gtin
Since you have escaped embedded JSON, you could try extracting in stages.
| spath
| spath input=body
| spath input=Message
not working.
my query is : table cxlBusinessData.data.body| spath | spath input=body | spath input=Message
Sounds like your raw event is not pure JSON - please can you share an anonymised version of your raw events in a code block </>
the previous result was belong from this data body
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
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 .
You need to remove spurious characters first.
| eval "cxlBusinessData.data.body" = replace('c', "\\\n ", "") | spath input=cxlBusinessData.data.body | spath input=Message | table gtin