FOR JSON - Array of String

Question

In the below MS Doc
format-query-results-as-json-with-for-json-sql-server

The return result has an array of objects when multiple students fit the specific criteria like so

[
{"s":[{"StudentName":"Carla Cap"}]},
{"ClassName":"Algebra Math","s":[{"StudentName":"Alice Apple"}]},
{"ClassName":"Art Painting","s":[{"StudentName":"Betty Boot"}]},
{"ClassName":"Calculus Math","s":[{"StudentName":"Alice Apple"},{"StudentName":"Betty Boot"}]}
]

Is there any way to make this an array of string and more like

[
{"s":["Carla Cap"]},
{"ClassName":"Algebra Math","s":["Alice Apple"]},
{"ClassName":"Art Painting","s":["Betty Boot"]},
{"ClassName":"Calculus Math","s":["Alice Apple","Betty Boot"]}
]

Answer 1

Hi @Ryan Abbey ,

Please refer below and check whether it is helpful to you.

SELECT   
REPLACE(   
REPLACE( (SELECT  
c.ClassName,  
s.StudentName  
from #tabClass   as c  
RIGHT OUTER JOIN #tabStudent as s ON s.ClassGuid = c.ClassGuid  
order by  
c.ClassName,  
s.StudentName  
FOR  
JSON AUTO),'{"StudentName":','' ),  
'"}','"' )  

Output:

[{"s":["Carla Cap"]},  
{"ClassName":"Algebra Math","s":["Alice Apple"]},  
{"ClassName":"Art Painting","s":["Betty Boot"]},  
{"ClassName":"Calculus Math","s":["Alice Apple","Betty Boot"]}]  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Ryan Abbey ,

In your situation, you would like to remove the '{"StudentName":' from the JSON.

Say we rename the StudentName as null or blank, we would get the error like below:

Then it is not possible for us to work hard on the select statement.

The only choice is to work hard after the JSON generated and use some functions like REPLACE or JSON_MODIFY to remove the '{"StudentName":' part.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.