I have a SQL table containing 2 columns – first Column is the ID of the users and the second column holds the JSON Phone information. The phone information consist of Work, Home phones and whether it is a mobile or a landline. It is not always that a user have both – mobile and Landline.
I’m now trying to select the users work phones so I have separate columns for the User ID, the Work Mobile number and the Work Landline Number. So far I have managed to get on type of phone information – the Work/mobile. But I can’t figure out how to also get the Work/Landline information in the same select. And if a user on has the landline, then mobile should be NULL in the column and also the other way. I have tried to add an extra CROSS APPLY with the where clause = Landline. But it just makes things worse…
This is What I’m trying to achieve
Any help is appreciated
This is the JSON string from the table
[
{
"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"
}
]
}
}
]
This is the T-SQL query I’m using
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'
2
Answers
You can use some conditional aggregation as below (DB Fiddle)
Please try the following solution.
SQL
Output