How to extract JSON from sql query?

My query looks like this:

 select MESSAGE, OFFSET from SRV_TABLE where STATUS='404';

I want to retry the messages that failed, but the message is a json string and the offset is a regular string. I need to extract the json messages and the offset strings and then iterate through each row. How would I do this? RIght now I am doing something like this:

public void retryFailedMessages() {
    JsonNode failedMessages = service.getFailedMessages();
    Iterator<JsonNode> iter = failedMessages.elements();
    while(iter.hasNext()) {
        JsonNode message = iter.next();
        String mess = message.get("MESSAGE").toString();
        String offset = message.get("OFFSET").toString();
        service.retry(mess, offset);
    }

}

//Service

String responseString = "";
JsonNode responseObj = null;
List<String> responseList;

try{
    responseLIst = dataAccessLayer.getFailedMessages();
    responseString = buildQueryResult(responseList);
    responseObj = objectMapper.readTree(responseString);
} catch( Exception e) {
    e.printStackTrace();
}

return responseObj;

buildQueryResult() simply appends all the strings together so the mapper can read it altogether. The mapper ignores the offset and doesn’t include it in the json, bceause it isn’t a valid json. How do I keep the offset and message and pass both to the service.retry() method?

Answer

You need to convert your string/json to a POJO or another way to convert it to object. I recomend you to use Jackson library and map it to a pojo.

Another way is to use some tools from the database to query directly:

select 
    JSON_VALUE(MESSAGE, '$.your.query') as query,
    JSON_VALUE(MESSAGE, '$.another.query') as another,
    OFFSET
from SRV_TABLE where STATUS='404';

In that way you can handle the values as a string at your code.

Can read more here: https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-ver15