Http response, json without backslashes

General
1

Hi, I try to make simple API for mssql database and I get it working. MSSQL return JSON directly nicely with "FOR JSON PATH"

image
image746×131 7.38 KB

But response is escaped with backslashes, how I can removed those backslashes?

"{\"ProductCode\":\"0040001\",\"ProductName\":\"Brother DK11209 osoitetarra 29x62mm\"}"

I need response to be like

"{"ProductCode":"0040001","ProductName":"Brother DK11209 osoitetarra 29x62mm"}"
2

Hi @timnis

Where are you seeing those backslashes?

Have you added a Debug node to see exactly what you are passing to the HTTP Response node?

3

@knolleary sorry for late answer.

Debug node shows json without backslashes, but if I test with firefox or Postman I see backslashes. In

I running NR on docker (official latest).

4

Can you show us what the debug node shows. You say it shows JSON, which implies that it is a string, but if it is a string then you need the escaped inner quotes.

5

Sure, here it is

image
image773×147 5.47 KB

And same time Postman shows

image
image929×224 13.4 KB

6

here is export of it

[{"id":"56854e06.f4b4e8","type":"http in","z":"710faa96.defddc","name":"","url":"/testi/:code","method":"get","upload":false,"swaggerDoc":"","x":110,"y":240,"wires":[["e116542b.46ac18"]]},{"id":"e116542b.46ac18","type":"MSSQL","z":"710faa96.defddc","mssqlCN":"5def4f8f.da2d1","name":"","query":"SELECT v.KOODI AS ProductCode, \nv.NIMIKE AS ProductName\nFROM VARASTO v \nWHERE v.KOODI = '{{{req.params.code}}}'\nFOR JSON PATH, without_array_wrapper, INCLUDE_NULL_VALUES","outField":"payload","returnType":0,"throwErrors":1,"x":300,"y":240,"wires":[["8dbdeeba.c14ad","57c0287.3fb52d8"]]},{"id":"8dbdeeba.c14ad","type":"http response","z":"710faa96.defddc","name":"","statusCode":"","headers":{"content-type":"application/json"},"x":530,"y":240,"wires":},{"id":"5def4f8f.da2d1","type":"MSSQL-CN","z":"","tdsVersion":"7_4","name":"vm","server":"172.19.0.4","port":"1433","encyption":true,"database":"YR1","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5"}]

7

@timnis postman shows that because you are returning a JavaScript Object with a key of JSON_F525.... and a value that is a JSON String. You know it is a string because it is surrounded by " and to keep it a valid string, the inner " have been escaped with the backslash.

So the answer here is to parse the JSON string value of the JSON_F525... property before returning it. You can use the JSON node to do that.

Btw, when you share flow JSON, please make sure you use the </> button in the formatting toolbar to format it properly, otherwise the forum will badly format your JSON and make it impossible to import.

8

@knolleary Thanks, I added JSON node just before http response and selected "Always convert to JSON Strings" but it won't help. What I did wrong?

Now I have

image
image968×153 8.28 KB

Export below

[{"id":"ed775a11.702ec8","type":"http in","z":"9655b2c6.4f50b8","name":"","url":"/testi/:code","method":"get","upload":false,"swaggerDoc":"","x":110,"y":240,"wires":[["ce848e27.cbee38"]]},{"id":"ce848e27.cbee38","type":"MSSQL","z":"9655b2c6.4f50b8","mssqlCN":"e9370435.63fbb8","name":"","query":"SELECT v.KOODI AS ProductCode, \nv.NIMIKE AS ProductName\nFROM VARASTO v \nWHERE v.KOODI = '{{{req.params.code}}}'\nFOR JSON PATH, without_array_wrapper, INCLUDE_NULL_VALUES","outField":"payload","returnType":0,"throwErrors":1,"x":380,"y":240,"wires":[["3be57d05.0011ba"]]},{"id":"d905e261.dc224","type":"http response","z":"9655b2c6.4f50b8","name":"","statusCode":"","headers":{"content-type":"application/json"},"x":870,"y":240,"wires":[]},{"id":"b5693abb.e7eca8","type":"debug","z":"9655b2c6.4f50b8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":870,"y":320,"wires":[]},{"id":"3be57d05.0011ba","type":"json","z":"9655b2c6.4f50b8","name":"","property":"payload","action":"str","pretty":true,"x":590,"y":240,"wires":[["d905e261.dc224","b5693abb.e7eca8"]]},{"id":"e9370435.63fbb8","type":"MSSQL-CN","z":"","tdsVersion":"7_4","name":"vm","server":"172.19.0.4","port":"1433","encyption":true,"database":"YR1","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5"}]
9

You have the JSON node configured to parse msg.payload. But from the postman screenshot you shared, the property that needs to be parsed is msg.payload[0]["JSON_F52E2B........."].

So the question is whether that is always the property you want to parse, or does the JSON_F52E2B... key change depending on the query you are running? If so, you'll need to use a Function node to pull apart the response of the SQL node and parse the appropriate part.

10

Its looks like to be always same if you use mssql server 2016 or 2017 with JSON feature.

More info

11

here is more accurate info

12

Hi @timnis - so if you change the JSON node to parse msg.payload[0]["JSON_F52E2B61-18A1-11d1-B105-00805F49916B"] then you should be all set.

13

@knolleary Thanks.

I tested to add config option to nodes config and it worked, directly like it should. So I ended up opening issue for the problem.

https://github.com/bestlong/node-red-contrib-mssql-plus/issues/20