从JSON数组提取不同值：SQL拆分工作移动与固定电话列

我有一张SQL表，包含两列：第一列为用户ID（[wd:Worker_ID]），第二列[wd:Personal_Data]存储JSON格式的电话信息。电话信息包含工作、家用两种使用类型，以及**Mobile（移动电话）、Landline（固定电话）**两种设备类型，并非所有用户同时拥有移动和固定电话。

我需要查询出三列数据：用户ID、工作移动电话号码、工作固定电话号码。如果用户仅拥有其中一种，另一列值为NULL。目前我只能单独查询出工作移动电话信息，无法在同一SELECT语句中同时获取工作固定电话信息，尝试添加额外CROSS APPLY并筛选固定电话也没成功。

期望结果 #

期望查询结果格式如下：

JSON字符串示例 #

[{"wd:Contact_Data": {"wd:Phone_Data": [{"@wd:Phone_Number_Without_Area_Code": "87654321","@wd:E164_Formatted_Phone": "+4512345678","@wd:Workday_Traditional_Formatted_Phone": "+45  87654321","@wd:National_Formatted_Phone": "87 65 43 21","@wd:International_Formatted_Phone": "+45 87 65 43 21","@wd:Tenant_Formatted_Phone": "+45 87 65 43 21","wd:Country_ISO_Code": "DNK","wd:International_Phone_Code": "45","wd:Phone_Number": "87654321","wd:Phone_Device_Type_Reference": {"@wd:Descriptor": "Mobile","wd:ID": [{"@wd:type": "WID","#text": "e57e6863118d011f540d34d4e62a1e2e"},{"@wd:type": "Phone_Device_Type_ID","#text": "Mobile"}]} ,"wd:Usage_Data": {"@wd:Public": "0","wd:Type_Data": {"@wd:Primary": "1","wd:Type_Reference": {"@wd:Descriptor": "Home","wd:ID": [{"@wd:type": "WID","#text": "836cf00ef5974ac08b786079866c946f"},{"@wd:type": "Communication_Usage_Type_ID","#text": "HOME"}]}}},"wd:Phone_Reference": {"@wd:Descriptor": "PHONE_REFERENCE-3-1163264","wd:ID": [{"@wd:type": "WID","#text": "66cf6935f30301489a12a247d26f67b8"},{"@wd:type": "Phone_ID","#text": "PHONE_REFERENCE-3-1163264"}]},"wd:ID": "PHONE_REFERENCE-3-1163264"},{"@wd:Phone_Number_Without_Area_Code": "12345678","@wd:E164_Formatted_Phone": "+4512345678","@wd:Workday_Traditional_Formatted_Phone": "+45  12345678","@wd:National_Formatted_Phone": "12 34 56 78","@wd:International_Formatted_Phone": "+45 12 34 56 78","@wd:Tenant_Formatted_Phone": "+45 12 34 56 78","wd:Country_ISO_Code": "DNK","wd:International_Phone_Code": "45","wd:Phone_Number": "12345678","wd:Phone_Device_Type_Reference": {"@wd:Descriptor": "Mobile","wd:ID": [{"@wd:type": "WID","#text": "e57e6863118d011f540d34d4e62a1e2e"},{"@wd:type": "Phone_Device_Type_ID","#text": "Mobile"}]},"wd:Usage_Data": {"@wd:Public": "1","wd:Type_Data": {"@wd:Primary": "1","wd:Type_Reference": {"@wd:Descriptor": "Work","wd:ID": [{"@wd:type": "WID","#text": "1f27f250dfaa4724ab1e1617174281e4"},{"@wd:type": "Communication_Usage_Type_ID","#text": "WORK"}]}}},"wd:Phone_Reference": {"@wd:Descriptor": "PHONE_REFERENCE-3-1163265","wd:ID": [{"@wd:type": "WID","#text": "66cf6935f30301d0e23ba247d26f6ab8"},{"@wd:type": "Phone_ID","#text": "PHONE_REFERENCE-3-1163265"}]},"wd:ID": "PHONE_REFERENCE-3-1163265"},{"@wd:Phone_Number_Without_Area_Code": "12341234","@wd:E164_Formatted_Phone": "+4512341234","@wd:Workday_Traditional_Formatted_Phone": "+45  12341234","@wd:National_Formatted_Phone": "12 34 12 34","@wd:International_Formatted_Phone": "+45 12 34 12 34","@wd:Tenant_Formatted_Phone": "+45 12 34 12 34","wd:Country_ISO_Code": "DNK","wd:International_Phone_Code": "45","wd:Phone_Number": "12 34 12 34","wd:Phone_Device_Type_Reference": {"@wd:Descriptor": "Landline","wd:ID": [{"@wd:type": "WID","#text": "e57e6863118d01df5fc54ad4e62a202e"},{"@wd:type": "Phone_Device_Type_ID","#text": "Landline"}]},"wd:Usage_Data": {"@wd:Public": "1","wd:Type_Data": {"@wd:Primary": "0","wd:Type_Reference": {"@wd:Descriptor": "Work","wd:ID": [{"@wd:type": "WID","#text": "1f27f250dfaa4724ab1e1617174281e4"},{"@wd:type": "Communication_Usage_Type_ID","#text": "WORK"}]}}},"wd:Phone_Reference": {"@wd:Descriptor": "PHONE_REFERENCE-3-1971570","wd:ID": [{"@wd:type": "WID","#text": "a335f9772da601cba2fc89c09701d471"},{"@wd:type": "Phone_ID","#text": "PHONE_REFERENCE-3-1971570"}]},"wd:ID": "PHONE_REFERENCE-3-1971570"}]}}]

当前使用的T-SQL查询语句 #

SELECT [wd:Worker_ID] AS Worker_ID,
       [Work_Mobile_Number]   
     
FROM [Employee_Master_Data_Source] 

CROSS APPLY OPENJSON ([wd:Personal_Data])
WITH (Phone_Details NVARCHAR(MAX) '$."wd:Contact_Data"."wd:Phone_Data"' AS JSON)

CROSS APPLY OPENJSON (Phone_Details)
WITH (Work_Mobile_Number    NVARCHAR(50) '$."@wd:Phone_Number_Without_Area_Code"',
      Phone_Usage           NVARCHAR(50) '$."wd:Usage_Data"."wd:Type_Data"."wd:Type_Reference"."@wd:Descriptor"',
      Phone_Type            NVARCHAR(50) '$."wd:Phone_Device_Type_Reference"."@wd:Descriptor"')

WHERE Phone_Usage = 'Work'
AND   Phone_Type  = 'Mobile'

解决方案 #

方案一：条件聚合（推荐，仅解析一次JSON） #

通过CASE语句结合聚合函数，将同一用户的工作移动和固定电话分别映射到对应列：

SELECT 
    emds.[wd:Worker_ID] AS Worker_ID,
    MAX(CASE WHEN pd.Phone_Type = 'Mobile' THEN pd.Phone_Number END) AS Work_Mobile_Number,
    MAX(CASE WHEN pd.Phone_Type = 'Landline' THEN pd.Phone_Number END) AS Work_Landline_Number
FROM [Employee_Master_Data_Source] emds
CROSS APPLY OPENJSON (emds.[wd:Personal_Data])
WITH (Phone_Details NVARCHAR(MAX) '$."wd:Contact_Data"."wd:Phone_Data"' AS JSON)
CROSS APPLY OPENJSON (Phone_Details)
WITH (
    Phone_Number    NVARCHAR(50) '$."@wd:Phone_Number_Without_Area_Code"',
    Phone_Usage     NVARCHAR(50) '$."wd:Usage_Data"."wd:Type_Data"."wd:Type_Reference"."@wd:Descriptor"',
    Phone_Type      NVARCHAR(50) '$."wd:Phone_Device_Type_Reference"."@wd:Descriptor"'
) pd
WHERE pd.Phone_Usage = 'Work'
GROUP BY emds.[wd:Worker_ID]

方案二：双OUTER APPLY分别筛选 #

通过两个独立的OUTER APPLY分别提取工作移动和固定电话，缺失时返回NULL：

SELECT 
    emds.[wd:Worker_ID] AS Worker_ID,
    pm.Work_Mobile_Number,
    pl.Work_Landline_Number
FROM [Employee_Master_Data_Source] emds
CROSS APPLY OPENJSON (emds.[wd:Personal_Data])
WITH (Phone_Details NVARCHAR(MAX) '$."wd:Contact_Data"."wd:Phone_Data"' AS JSON)
OUTER APPLY (
    SELECT TOP 1 
        j.Work_Mobile_Number
    FROM OPENJSON (Phone_Details)
    WITH (
        Phone_Usage           NVARCHAR(50) '$."wd:Usage_Data"."wd:Type_Data"."wd:Type_Reference"."@wd:Descriptor"',
        Phone_Type            NVARCHAR(50) '$."wd:Phone_Device_Type_Reference"."@wd:Descriptor"',
        Work_Mobile_Number    NVARCHAR(50) '$."@wd:Phone_Number_Without_Area_Code"'
    ) j
    WHERE j.Phone_Usage = 'Work' AND j.Phone_Type = 'Mobile'
) pm
OUTER APPLY (
    SELECT TOP 1 
        j.Work_Landline_Number
    FROM OPENJSON (Phone_Details)
    WITH (
        Phone_usage           NVARCHAR(50) '$."wd:Usage_Data"."wd:Type_Data"."wd:Type_Reference"."@wd:Descriptor"',
        Phone_Type            NVARCHAR(50) '$."wd:Phone_Device_Type_Reference"."@wd:Descriptor"',
        Work_Landline_Number  NVARCHAR(50) '$."@wd:Phone_Number_Without_Area_Code"'
    ) j
    WHERE j.Phone_Usage = 'Work' AND j.Phone_Type = 'Landline'
) pl

内容的提问来源于stack exchange，提问作者OJ Slott