0

i could not able to parse the below json value , I tried with parse_json() and todynamic() ,I m getting the result column values to be empty

enter image description here]1

vaishnavi
  • 85
  • 4
  • 14
  • it would be helpful if you could include the input data and query as text, instead of as (or in addition to) an image – Yoni L. Jun 03 '20 at 18:52
  • you may also want to check if your question isn't already answered here: https://stackoverflow.com/a/57403973/7861807 – Yoni L. Jun 03 '20 at 18:53
  • Thank you @YoniLeibowitz , but I m still getting empty value for my result. Below is my query app('usl-exepipe-ai-dev').['vsubram1'] | extend log = parse_json(message).artifactResult.Log | extend log_json = parse_json(log) | extend stdout_code = tostring(parse_json(log_json)[0]["code"]) | project stdout_code – vaishnavi Jun 03 '20 at 20:05
  • and the data? can you paste that as a string in your original question? – Yoni L. Jun 03 '20 at 20:07
  • i don't think anyone except for you will have access to your resource. and the data set you've provided is too bulky. how about a minimal example, without all those `\r`s and `\n`? to make sure it's exactly like in your environment? – Yoni L. Jun 03 '20 at 20:30
  • Thank you @YoniLeibowitz. I have updated data to have only one row of message column where I m trying to parse 'Log' which has two set of data in it – vaishnavi Jun 03 '20 at 21:19
  • I could able to parse it with todynamic function | extend log_json = todynamic(log) | extend stdout_code = todynamic(log_json)[0]["code"] – vaishnavi Jun 03 '20 at 21:34

1 Answers1

2

the issue is that your payload includes an internal invalid JSON payload. it is possible to "fix" it using the query language (see usages of replace() in the example below), however it'd be best if you can write a valid JSON payload to begin with.

try running this:

print s = @'{"pipelineId":"63dfc1f6-5a43-5bca-bffe-6a36a435e19d","vmId":"9252382a-814f-4d02-9b1b-305db4caa208/usl-exepipe-dev/westus/usl-exepipe-lab-dev/asuvp306563","artifactResult":{"Id":"execution-job-2","SourceName":"USL Repository","ArtifactName":"install-lcu","Status":"Succeeded","Parameters":null,"Log":"[{\"code\":\"ComponentStatus/StdOut/succeeded\",\"level\":\"Info\",\"displayStatus\":\"Provisioning succeeded\",\"message\":\"2020-06-02T14:33:04.711Z | I | Starting artifact ''install-lcu''\r\n2020-06-02T14:33:04.867Z | I | Starting Installation\r\n2020-06-02T14:33:04.899Z | I | C:\\USL\\LCU\\4556803.msu Exists.\r\n2020-06-02T14:33:04.914Z | I | Starting installation process ''C:\\USL\\LCU\\4556803.msu /quiet /norestart''\r\n2020-06-02T14:43:14.169Z | I | Process completed with exit code ''3010''\r\n2020-06-02T14:43:14.200Z | I | Need to restart computer after hotfix 4556803 installation\r\n2020-06-02T14:43:14.200Z | I | Finished Installation\r\n2020-06-02T14:43:14.200Z | I | Artifact ''install-lcu'' succeeded\r\n\",\"time\":null},{\"code\":\"ComponentStatus/StdErr/succeeded\",\"level\":\"Info\",\"displayStatus\":\"Provisioning succeeded\",\"message\":\"\",\"time\":null}]","DeploymentLog":null,"StartTime":"2020-06-02T14:32:40.9882134Z","ExecutionTime":"00:11:21.2468597","BSODCount":0},"attempt":1,"instanceId":"a301aaa0c2394e76832867bfeec04b5d:0","parentInstanceId":"78d0b036a5c548ecaafc5e47dcc76ee4:2","eventName":"Artifact Result"}'
| mv-expand log = parse_json(replace("\r\n", " ", replace(@"\\", @"\\\\", tostring(parse_json(tostring(parse_json(s).artifactResult)).Log))))
| project log.code, log.level, log.displayStatus, log.message
Yoni L.
  • 22,627
  • 2
  • 29
  • 48