SQL 到 JSON - 将结果分组到 JSON 数组中

我正在尝试提出一个 SQL 解决方案来安排输出以匹配预期的 JSON 格式。

我有一些简单的 SQL 来突出显示问题的根源；

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'
,'work' AS 'telecom.use'
,'phone' AS 'telecom.system'
,'12344556' AS 'telecom.value'
FROM tblCustomer
FOR json path

这将返回 JSON 为；

[
{
    "name": {
        "family": "Surname",
        "given": "Forename, Middle Name",
        "prefix": "Title"
    },
    "birthdate": "2019-02-13T12:06:45.490",
    "gender": "F",
    "active": "Yes",
    "telecom": {
        "use": "work",
        "system": "phone",
        "value": "12344556"
    }
}
]

我需要的是将额外的对象添加到“电信”数组中，使其显示为；

[
{
    "name": {
        "family": "Surname",
        "given": "Forename, Middle Name",
        "prefix": "Title"
    },
    "birthdate": "2019-02-13T12:06:45.490",
    "gender": "F",
    "active": "Yes",
    "telecom": {
        "use": "work",
        "system": "phone",
        "value": "12344556"
    },
    {
        "use": "work",
        "system": "home",
        "value": "12344556"
    },
}
]

我错误地假设我可以继续添加到我的 SQL 中，如下所示；

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'
,'work' AS 'telecom.use'
,'phone' AS 'telecom.system'
,'12344556' AS 'telecom.value'
,'home' AS 'telecom.use'
FROM tblCustomer
FOR json path

然而，它会按照我的命名缩进嵌套项目；

由于以下原因，无法在 JSON 输出中生成属性“telecom.use” 与其他列名或别名冲突。使用不同的名称和 SELECT 列表中每列的别名。

有没有办法用 SQL 处理这种嵌套，或者我需要为 JSON 查询创建单独的查询并将它们合并？

Thanks

使用@@版本 Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) 2017 年 8 月 22 日 17:04:49 版权所有 (C) 2017 Microsoft Corporation Windows Server 2012 R2 Datacenter 6.3 上的 Express 版（64 位） （内部版本 9600：）（管理程序）

对问题进行小编辑以使用动态值而不是强制静态成员。

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'
    ,'work' AS 'telecom.use'
    ,'phone' AS 'telecom.system'
    ,customerWorkTelephone AS 'telecom.value'
    ,'home' AS 'telecom.use'
    ,'phone' AS 'telecom.system'
    ,customerHomeTelephone AS 'telecom.value'
FROM tblCustomer
FOR json path

“值”项将从 tblCustomer 表中的列中获取。我试图很好地处理下面的响应，但无法在子查询中获得完全正确的逻辑。

再次感谢

进一步编辑

我有一些 SQL 给了我期望的输出，但是我不确定它是最好的，我的方法是否不是最优的？

SELECT TOP 1 [name.family] = 'Surname'
,[name.given] = 'Forename, Middle Name'
,[name.prefix] = 'Title'
,[birthdate] = GETDATE()
,[gender] = 'F'
,[active] = 'Yes'
,[telecom] = (
    SELECT [use] = V.used
        ,[system] = 'phone'
        ,[value] = CASE V.used
            WHEN 'work'
                THEN cu.customerWorkTelephone
            WHEN 'home'
                THEN cu.customerHomeTelephone
            when 'mobile'
                then cu.customerMobileTelephone
            END
    FROM (
        VALUES ('work')
            ,('home')
            ,('mobile')
        ) AS V(used)

    FOR json path
    )
FROM tblCustomer cu
FOR JSON PATH

使用带有一些硬编码行的子选择：

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 tblCustomer
FOR JSON PATH

注意缺少telecom.子查询内的前缀。

结果（没有表格参考）：

[
    {
        "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：特别是对于 SQL Server，我发现使用左侧的别名更具可读性：

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 tblCustomer
FOR JSON 
    PATH