How to exclude escape characters from ROOT when using FOR JSON from SQL server

Question

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?

Answer 1

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.:

SELECT
  'CHMCT' as [FORMATCODE],
  'PA_B4B_AUX_CHMCT_ISRReference' as [TYPE],
  (
    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
  ) DATA
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

Which will yield output similar to...

{
    "FORMATCODE": "CHMCT",
    "TYPE": "PA_B4B_AUX_CHMCT_ISRReference",
    "DATA": [
        {
            "ACCOUNT_REF": "...",
            "SUN_DB": "PK1",
            "REFERENCE": "...",
            "ISR_NUMBER": "...",
            "CHECK_DIGIT": "",
            "PAID_DATE": "",
            "PAID_FLAG": ""
        }
    ]
}