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

OMayhew 1 Reputation point
2022-05-06T09:21:43.973+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,333 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Viorel 114.6K Reputation points
    2022-05-06T09:46:28.637+00:00

    Try something like this:

    SELECT 
        FORMATCODE = 'CHMCT',
        [TYPE] = 'PA_B4B_AUX_CHMCT_ISRReference',
        [DATA] = (
           SELECT rtrim(a.ACCNT_CODE) as ACCOUNT_REF, 'PK1' as SUN_DB,
           . . . . . ( your query without ROOT) . . . . .
        )
    FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
    
    0 comments No comments

  2. Olaf Helper 43,166 Reputation points
    2022-05-06T09:55:10.55+00:00

    How can I exclude the backslashes from the ROOT element?

    Remove the double quotation marks from the root definition.

    FOR JSON PATH, INCLUDE_NULL_VALUES, ROOT ('FORMATCODE:CHMCT, TYPE:PA_B4B_AUX_CHMCT_ISRReference,DATA')
    
    0 comments No comments

  3. Seeya Xi-MSFT 16,461 Reputation points
    2022-05-09T02:33:23.007+00:00

    Hi @OMayhew ,

    Welcome to Microsoft Q&A!
    Here is a related document which may give you some help: https://learn.microsoft.com/en-us/sql/relational-databases/json/solve-common-issues-with-json-in-sql-server?view=sql-server-ver15

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments