T-SQL JSON path nested into array-CodePudding

I am bumping my head around some (I think stupid) T-SQL piece of code. With some SQL data I am creating a HTTP request body to perform an API call, I know that T-SQL/SQL is not the way to go for these operations. But the application where I am configuring for does not have an option.

The query (made fictive with "0 = 0" case etc.):

DECLARE @body VARCHAR(MAX)

SET @body = (
        SELECT TOP 1
        CASE WHEN 0 = 0 THEN 'OUTBOUND' ELSE 'INBOUND' END AS direction, 
        CASE WHEN 0 = 0 THEN CAST('true' AS BIT) ELSE CAST('false' AS BIT) END AS isAlone,
        CASE WHEN 0 = 0 THEN 
         'AB123'
        END AS Registration
        FROM table
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        )

SELECT @body

This spits out a JSON nicely:

{
    "direction": "OUTBOUND",
    "isAlone": true,
    "Registration": "AB123"
}

So far so good. But the API where I am talking to wants the following for the "Registration" key:

{
    "direction": "OUTBOUND",
    "isAlone": true,
    "Registration": [
        "AB123"
    ]
}

So I need to wrap the value for the "Registration" in an array.

I have tried to:

Perform string manipulation ( '[' )
OUTER APPLY within the @body
substring (SELECT FROM FOR JSON PATH)

Somehow I cannot figure out how I can make this work.. Can you guys push me into the right direction?

Many thanks in common!

CodePudding user response：

If you use JSON_QUERY without a path, it will tell SQL to treat the string as JSON so FOR JSON PATH will not affect the formatting. Behavior is documented here

Create JSON Array Column in Natively using JSON_QUERY()

CREATE TABLE #test (direction Varchar(100),isAlone bit,Registration Varchar(100))
INSERT INTO #test
VALUES ('OUTBOUND',1,'AB123')

DECLARE @body VARCHAR(1000) = (
SELECT direction
    ,isAlone
    ,Registration = JSON_QUERY(CONCAT('["',registration,'"]'))   
FROM #test
FOR JSON PATH,WITHOUT_ARRAY_WRAPPER 
)

SELECT @body