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?

Your query will not run as JSON names can't be the same within the same object. Please correct — Serg
– Serg, Commented Mar 17, 2022 at 17:35
@Serg Yes it is wrong, So if i want to add another object in my array, so how should i proceed in this case? — blac040
– blac040, Commented Mar 17, 2022 at 17:41

Kevin · Accepted Answer · 2022-03-17 18:04:27Z

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.

edited Mar 17, 2022 at 18:04

answered Mar 17, 2022 at 17:54

Kevin

3141 silver badge10 bronze badges

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

4 Comments

blac040 Over a year ago

Thanks for the answer. But if my desired output should be like { "Society":"Engliand", "Students":[ { "Id":{ "FirstName":"Harry" } }, { "School":{ "School_Name":"St.Stephan School" } } ] }

blac040 Over a year ago

Then I think root may not work

blac040 Over a year ago

And root and without array wrapper can't be used together

Kevin Over a year ago

Okay... I think dropping the command to skip the array wrapper could give you what you were looking for. I don't have a way to test it.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related