FOR JSON PATH in CASE statement

Question

When I use a variable (or a field from a table) in a case statement with "FOR JSON PATH", the JSON provided is not well formed. Ex:

declare @MyValue nvarchar(50)
set @MyValue='1'
select CASE WHEN @MyValue='1' THEN (select 'ROLE_CLIENT_READONLY' as id   FOR JSON PATH) end as [Role]  FOR JSON PATH

Return
[{"Role":"[{\"id\":\"ROLE_CLIENT_READONLY\"}]"}]

But if I put this, it's works:

select CASE WHEN '1'='1' THEN (select 'ROLE_CLIENT_READONLY' as id   FOR JSON PATH) end as [Role]  FOR JSON PATH

Return    
[{"Role":[{"id":"ROLE_CLIENT_READONLY"}]}]

Any idea on the reason for this behavior? How can I fix this in the first scenario?

Answer 1

Not sure why it treats one different than the other. I certainly would not expect the difference in JSON between using a variable in the query and a string literal.

Interestingly
CASE WHEN CAST(N'1' AS NVARCHAR(MAX)) = CAST(N'1' AS NVARCHAR(MAX))
also produces the problematic JSON, however
CASE WHEN CAST(N'1' AS NVARCHAR(50)) = CAST(N'1' AS NVARCHAR(50))
does not.

This seems to work as a workaround for using the variable in the query:

WITH ids AS (
    SELECT CASE WHEN @MyValue = '1' THEN 'ROLE_CLIENT_READONLY' END id
)
SELECT (SELECT id FROM ids FOR JSON PATH) AS [Role] FOR JSON PATH;