SQL to JSON - Grouping Results into JSON Array SQL to JSON - Grouping Results into JSON Array json json

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