Skip to main content
T
TT_143969215-Regular Member
5-Regular Member
November 12, 2025
Solved

SQL to JSON Kepware

  • November 12, 2025
  • 1 reply
  • 330 views

Hello, I am a new user to Kepware, I setting up a flow of SQL data -> Kepware (querry to json) -> IoT gateway (REST server).Although the data in the OPC quick view look fine (attatch picture), the output from iot gateway contain un wanted character, how do I clean that ?

 

{
 "readResults": [
 {
 "id": "SQL2JSON.SQL2JSON.JsonData",
 "s": true,
 "r": "",
 "v": "[{\"id\": 1, \"pressure\": \"10\", \"temperature\": \"1\"}, {\"id\": 2, \"pressure\": \"190\", \"temperature\": \"1\"}, {\"id\": 3, \"pressure\": \"170\", \"temperature\": \"1\"}, {\"id\": 4, \"pressure\": \"0\", \"temperature\": \"0\"}, {\"id\": 5, \"pressure\": \"0\", \"temperature\": \"0\"}, {\"id\": 6, \"pressure\": \"118\", \"temperature\": \"70\"}, {\"id\": 7, \"pressure\": \"118\", \"temperature\": \"70\"}, {\"id\": 8, \"pressure\": \"118\", \"temperature\": \"70\"}, {\"id\": 9, \"pressure\": \"118\", \"temperature\": \"70\"}, {\"id\": 10, \"pressure\": \"118\", \"temperature\": \"70\"}, {\"id\": 11, \"pressure\": \"118\", \"temperature\": \"70\"}, {\"id\": 12, \"pressure\": \"118\", \"temperature\": \"70\"}, {\"id\": 13, \"pressure\": \"118\", \"temperature\": \"70\"}, {\"id\": 14, \"pressure\": \"118\", \"temperature\": \"70\"}, {\"id\": 15, \"pressure\": \"118\", \"temperature\": \"70\"}, {\"id\": 16, \"pressure\": \"118\", \"temperature\": \"70\"}, {\"id\": 17, \"pressure\": \"118\", \"temperature\": \"70\"}, {\"id\": 18, \"pressure\": \"100\", \"temperature\": \"5\"}, {\"id\": 19, \"pressure\": \"100\", \"temperature\": \"5\"}, {\"id\": 20, \"pressure\": \"100\", \"temperature\": \"5\"}, {\"id\"",
 "t": 1762954502952
 }
 ]
}

 

Best answer by pshashipreetham

Hi TT,

 

The REST response is actually correct JSON. In this setup the SQL2JSON tag in Kepware is a String, so the IoT Gateway sends that string as the value of v. Because v itself is JSON text, all quotes are escaped in the outer JSON:

"v": "[{\"id\": 1, \"pressure\": \"10\", \"temperature\": \"1\"}, ... ]"

 

Those backslashes are just escape characters, not real extra data.

To get a clean JSON array on the REST client side, the response has to be handled in two steps:

  1. Parse the IoT Gateway response once to get the v field (as a string).

  2. Parse the content of v again as JSON to obtain the real array of objects.

Example in pseudocode:

outer = json.parse(http_response_body)
raw = outer.readResults[0].v // string with escaped quotes
data = json.parse(raw) // real JSON array

 

Kepware’s REST agent does not provide a setting to “remove” those escape characters on the server side, because the SQL2JSON result is stored as a string tag. Cleaning has to be done in the consumer (or, alternatively, by changing the design to expose individual columns as separate tags instead of one JSON string).

 

Thanks,

1 reply

P
18-Opal
pshashipreetham18-OpalAnswer
18-Opal
November 21, 2025

Hi TT,

 

The REST response is actually correct JSON. In this setup the SQL2JSON tag in Kepware is a String, so the IoT Gateway sends that string as the value of v. Because v itself is JSON text, all quotes are escaped in the outer JSON:

"v": "[{\"id\": 1, \"pressure\": \"10\", \"temperature\": \"1\"}, ... ]"

 

Those backslashes are just escape characters, not real extra data.

To get a clean JSON array on the REST client side, the response has to be handled in two steps:

  1. Parse the IoT Gateway response once to get the v field (as a string).

  2. Parse the content of v again as JSON to obtain the real array of objects.

Example in pseudocode:

outer = json.parse(http_response_body)
raw = outer.readResults[0].v // string with escaped quotes
data = json.parse(raw) // real JSON array

 

Kepware’s REST agent does not provide a setting to “remove” those escape characters on the server side, because the SQL2JSON result is stored as a string tag. Cleaning has to be done in the consumer (or, alternatively, by changing the design to expose individual columns as separate tags instead of one JSON string).

 

Thanks,

    T
    TT_143969215-Regular MemberAuthor
    5-Regular Member
    November 24, 2025

    Thanks a lot.
    Best regards

    Tran Duc Thinh

    Terms & ConditionsCookie settingsAccessibility statement