MySQL JSON Parsing - Escaped Json-within-Json

Question

I'm having trouble using the in-built MySQL JSON functions to parse out a value from a JSON string nested inside a JSON string.

Here's a sample:

{
  "SucceededAt": "2022-01-18T07:54:50.5548083Z",
  "PerformanceDuration": "1463",
  "Latency": "91",
  "Result": "\"Request Body: {\\\"request\\\":[{\\\"id\\\":[{\\\"value\\\":\\\"1\\\"}],\\\"roles\\\":{\\\"receiver\\\":{\\\"id\\\":[{\\\"value\\\":\\\"1115559991\\\"}]}},\\\"details\\\":{\\\"adjustmentAmount\\\":{\\\"value\\\":7800}}}]}, Response Body:{\\\"response\\\":[{\\\"id\\\":[{\\\"value\\\":\\\"1\\\"}],\\\"parts\\\":{\\\"specification\\\":{\\\"characteristicsValue\\\":[{\\\"characteristicName\\\":\\\"MSISDN\\\",\\\"value\\\":\\\"9998885556\\\"},{\\\"characteristicName\\\":\\\"ResponseCode\\\",\\\"value\\\":\\\"1000\\\"},{\\\"characteristicName\\\":\\\"ResponseDescription\\\",\\\"value\\\":\\\"Operation successfully.\\\"}]}}}]}\""
}

I want to get the "request" and response" key/value pairs from within the "Result" key/value.

When I use SELECT JSON_VALUE(Data, '$.Result') FROM [...] to extract the value from the "Result" key, it returns the escaped string value (which is again, json-within-json, I think) as follows (with the double-quote characters):

"Request Body: {\"request\":[{\"id\":[{\"value\":\"1\"}],\"roles\":{\"receiver\":{\"id\":[{\"value\":\"114787601\"}]}},\"details\":{\"adjustmentAmount\":{\"value\":7800}}}]}, Response Body:{\"response\":[{\"id\":[{\"value\":\"1\"}],\"parts\":{\"specification\":{\"characteristicsValue\":[{\"characteristicName\":\"MSISDN\",\"value\":\"114787601\"},{\"characteristicName\":\"ResponseCode\",\"value\":\"1000\"},{\"characteristicName\":\"ResponseDescription\",\"value\":\"Operation successfully.\"}]}}}]}"

This is the step I'm stuck at.

Is there a way to do this with the in-built MySQL JSON functions?

The value of `result` is not valid JSON, it needs `{}` around the object. — Barmar, Jan 18 '22 at 21:40
ahh, you're right. So Basically I need to clean up the results of the JSON_VALUE (add {}, remove the wrapping double quotes, and replace the escape charaters, then parse it as it's own JSON — GWR, Jan 18 '22 at 21:59
Yes, that should work. It won't be pretty, but nothing using JSON is pretty in MySQL. — Barmar, Jan 18 '22 at 21:59

score 1 · Accepted Answer · answered Jan 18 '22 at 22:27

You can use JSON_UNQUOTE to take the json string returned by JSON_VALUE and turn it into a raw string value. And then use substring_index to parse out the Request Body: and Response Body:, assuming those are formatted exactly as shown in your example (is there really a space after the colon for the request but not the response??):

select
    substring_index(substring_index(json_unquote(json_value(Data, '$.Result')),', Response Body:',1),'Request Body: ',-1) as request,
    substring_index(json_unquote(json_value(Data, '$.Result')),', Response Body:',-1) as response
from foo;

fiddle

substring_index(foo,bar,1) gets everything before the first bar in foo (or returns the entire string if bar is not found). substring_index(foo,bar,-1) gets everything after the last bar in foo (or returns the entire string if bar is not found).

MySQL JSON Parsing - Escaped Json-within-Json

1 Answers1