Get nest multiple section as JSON using Sql query

Question

I am giving my table Structure below

tblOrganization

OrganizationId

OrganizationName

OrganizatioEmail

tblLocation

LocationId

OrganizationId Here may contain multiple location aginist one Organization

LocationName

tblContact

ContactId

OrganizationId Here may contain multiple contact aginist one Organization

ContactName

tblSpecialities

SpecialityId

SpecialityName

tblOrganizationSpecialities

SpecialityId Mapping table Organization and Specialities. Here may contain multiple Specialities aginist one Organization

OrganizationId

Please help me write sql query to get below output

Expected Result

{
	"Data": [{
		"organizationId": 333,
		"organizationName": "Org3",
		"OrganizatioEmail": "Org1TIN3@org.com",
		"location": [
		  {"LocationName": "Location1"},
		  {"LocationName": "Location2"},
		  {"LocationName": "Location3"}
		],
		"contact": [
		  {"ContactName": "ContactName1"},
		  {"ContactName": "ContactName2"},
		  {"ContactName": "ContactName3"}
		],
		"specialities": [
		  {"specialityName": "Speciality1"},
		  {"specialityName": "Speciality2"},
		  {"specialityName": "Speciality3"}
		]

	}]
}

Answer 1

Try the following code:

SELECT 
	OrganizationId, 
	OrganizationName, 
	OrganizatioEmail, 
	(
		SELECT LocationName 
		FROM tblLocation 
		WHERE OrganizationId = O.OrganizationId 
		FOR JSON PATH
	) AS Location,
	(
		SELECT ContactName 
		FROM tblContact 
		WHERE OrganizationId = O.OrganizationId 
		FOR JSON PATH
	) AS Contact,
	(
		SELECT S.SpecialityName 
		FROM tblOrganizationSpecialities AS OS 
		INNER JOIN tblSpecialities AS S ON OS.SpecialityId = S.SpecialityId 
		WHERE OS.OrganizationId = O.OrganizationId
		FOR JSON PATH
	) AS Specialities
FROM tblOrganization AS O
FOR JSON PATH;

Answer 2

See Format Query Results as JSON with FOR JSON (SQL Server)

Answer 3

Hi @Binumon George

Have you check Guoxiong's answer? You could also use the ROOT option to specify a named root element, like this: FOR JSON PATH,ROOT('Data')

Best regards,

Cosmog Hong