Tsql to generate json array

Question

I am trying to generate json output from a sql table. Need help with the SQL statement please. "schemas" output is not coming as I expected. My sql query is returning extra '\'. Screenshot I indicated how my query should return the output as an array. Need help with fixing my select statement.

Thanks in advance.

Drop TABLE #tmp  
CREATE TABLE #tmp (  
    [EmployeeEmailAccount] [nvarchar](50) NULL,  
    [displayName] [nvarchar](50) NULL  
) ON [PRIMARY]  
GO  
INSERT #tmp ([EmployeeEmailAccount], [displayName]) VALUES (N'******@gmail.com', N'testusr1')  
GO   


SELECT TOP 1     
 [schemas]    = '["urn:scim:schemas:core:2.0:User" , "urn:scim:schemas:extension:fa:2.0:faUser"]',  
 EmployeeEmailAccount as 'userName'  
   FROM #tmp  
   FOR JSON PATH, WITHOUT_ARRAY_WRAPPER  

Answer 1

[schemas] = '[''urn:scim:schemas:core:2.0:User'' , ''urn:scim:schemas:extension:fa:2.0:faUser'']'

Answer 2

Try:

SELECT TOP 1   
  [schemas] = '[''urn:scim:schemas:core:2.0:User'' , ''urn:scim:schemas:extension:fa:2.0:faUser'']',
  EmployeeEmailAccount as 'userName'
FROM #tmp
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Output:

{"schemas":"['urn:scim:schemas:core:2.0:User' ,
'urn:scim:schemas:extension:fa:2.0:faUser']",
"userName":"******@gmail.com"}

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

To output an array without the unneeded ' and ", try this query too:

SELECT TOP 1   
    [schemas]    = json_query('["urn:scim:schemas:core:2.0:User" , "urn:scim:schemas:extension:fa:2.0:faUser"]'),
    EmployeeEmailAccount as 'userName'
FROM #tmp
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER