How to create array of object in JSON from data in SQL table by SQL query?

Question

I want to create array of object in json from sql query. So I created the SQL query as follows :

Select society as Society,
   (select Name as [ID.FirstName],schoolName as [School.School_Name] from Details for json path ) as Students
   
From Details
For json path, WITHOUT_ARRAY_WRAPPER

I wanted my JSON result should look like this:

{ "Society":"England",
 "Students":[
    {
      "Id":{
        "FirstName":"Harry"
      }
    },
    {
      "School":{
         "School_Name":"St.Stephan School"
       }
    }
  ]
 }
     

I am getting the different output but I know this is not the generic way to write the code.

{ "Society":"England",
  "Students":[
    {
      "Id":{
        "FirstName":"Harry"
      },
      "School":{
         "School_Name":"St.Stephan School"
       }
    }
  ]
 }

Can somebody please help me out?

Answer 1

I think this will work...

select [Name] as [ID.FirstName], schoolName as [School.School_Name] 
from Details 
for json path, root('Students')

I looked at the MS docs for the "for json" command. I don't think you need to split up your select fields the way you did. Also, I added the brackets around "Name" because memory is telling me that it's a reserved word, and you can force it to treat it as a field name with the brackets.