question

CzarR-3851 avatar image
0 Votes"
CzarR-3851 asked Viorel-1 answered

Tsql to generate json array

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'test1@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

128796-capture.png


sql-server-transact-sql
capture.png (9.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

stonez-0855 avatar image
0 Votes"
stonez-0855 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

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":"test1@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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.