SQL to JSON - Grouping Results into JSON Array
Using a subselect with a few hard-coded rows:
SELECT TOP 1 'Surname' AS 'name.family' ,'Forename, Middle Name' AS 'name.given' ,'Title' AS 'name.prefix' ,getDATE() AS 'birthdate' ,'F' AS 'gender' ,'Yes' AS 'active' ,'telecom' = ( SELECT 'work' AS 'use' ,V.system AS 'system' ,'12344556' AS 'value' FROM (VALUES ('phone'), ('home')) AS V(system) FOR JSON PATH)FROM tblCustomerFOR JSON PATH
Note the lack of the telecom.
prefix inside the subquery.
Results (without the table reference):
[ { "name": { "family": "Surname", "given": "Forename, Middle Name", "prefix": "Title" }, "birthdate": "2019-02-13T12:53:08.400", "gender": "F", "active": "Yes", "telecom": [ { "use": "work", "system": "phone", "value": "12344556" }, { "use": "work", "system": "home", "value": "12344556" } ] }]
PD: Particularly for SQL Server I find using the alias on the left side more readable:
SELECT TOP 1 [name.family] = 'Surname', [name.given] = 'Forename, Middle Name', [name.prefix] = 'Title', [birthdate] = GETDATE(), [gender] = 'F', [active] = 'Yes', [telecom] = ( SELECT [use] = 'work', [system] = V.system, [value] = '12344556' FROM (VALUES ('phone'), ('home')) AS V(system) FOR JSON PATH)FROM tblCustomerFOR JSON PATH
SELECT EMP.ID, EMP.NAME, DEP.NAMEFROM EMPLOYEE EMP INNER JOIN DEPARTMENT DEP ON EMP.DEPID=DEP.DEPIDWHERE EMP.SALARY>1000FOR JSON PATH