SQL to JSON Kepware

[TT_14396921]

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
    }
  ]
}

[pshashipreetham · Accepted solution]

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,

[TT_14396921]

Thanks a lot.
Best regards

Tran Duc Thinh