I am trying to create JSON output from SQL Server using FOR JSON. My JSON record is good however I have a number of elements I need in the ROOT section but this is resulting in escape characters which is causing issues.
My select statement is as follows;
SELECT RTRIM(a.ACCNT_CODE) AS ACCOUNT_REF,
'PK1' AS SUN_DB,
RTRIM(a.TREFERENCE) AS REFERENCE,
RTRIM(x.GNRL_DESCR_25) AS ISR_NUMBER,
'' AS CHECK_DIGIT,
'' AS PAID_DATE,
'' AS PAID_FLAG
FROM PK1_A_SALFLDG a
JOIN PK1_A_SALFLDG_LAD x ON a.ACCNT_CODE = x.ACCNT_CODE
AND a.JRNAL_NO = x.JRNAL_NO
AND a.JRNAL_LINE = x.JRNAL_LINE
WHERE LEN(RTRIM(x.GNRL_DESCR_25)) > 15
FOR JSON PATH, INCLUDE_NULL_VALUES, ROOT('FORMATCODE":"CHMCT","TYPE":"PA_B4B_AUX_CHMCT_ISRReference","DATA');
But the ROOT is being output as
{"FORMATCODE\\":\"CHMCT\\",\\"TYPE\\":\\"PA_B4B_AUX_CHMCT_ISRReference\\",\\"DATA":[
How can I exclude the backslashes from the ROOT element?
It seems like you're trying to embed extra JSON data into
ROOT
. Instead of trying to do that try instead to use a nested JSON query, e.g.:Which will yield output similar to...