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'[email protected]', 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

Ronen Ariely · Accepted Answer · 2021-09-03 09:42:53Z

1

To get array you can use the function JSON_QUERY

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 
GO

This will return:

{
  "schemas": [
    "urn:scim:schemas:core:2.0:User",
    "urn:scim:schemas:extension:fa:2.0:faUser"
  ],
  "userName": "[email protected]"
}

Note: to format the JSON as such I am using my SSMS extension, but you can use external third party app like notepad++ or VS

answered Sep 3, 2021 at 9:42

Ronen Ariely

2,45414 silver badges23 bronze badges

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

Collectives™ on Stack Overflow

Tsql to generate json array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related