T-SQL JSON path nested into array

Question

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!

Stephan · Accepted Answer · 2022-03-20 18:09:44Z

1

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

edited Mar 20, 2022 at 18:09

answered Mar 18, 2022 at 22:45

Stephan

6,0581 gold badge19 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

forRJ Over a year ago

Oh my.. That is it. I have only tried with the FOR JSON PATH included. Many thanks!

Collectives™ on Stack Overflow

T-SQL JSON path nested into array

1 Answer 1

Create JSON Array Column Natively using JSON_QUERY()

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Create JSON Array Column Natively using JSON_QUERY()

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related