jsonsql-servert-sqlsql-server-2019open-json
Get different values from JSON array
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'
Solution
You can use some conditional aggregation as below (DB Fiddle)
SELECT [wd:Worker_ID] AS Worker_ID,
ca.*
FROM [Employee_Master_Data_Source]
CROSS APPLY
(
SELECT
Work_Mobile_Number = MAX(CASE WHEN Phone_Usage = 'Work' AND Phone_Type = 'Mobile' THEN Phone_Number_Without_Area_Code END),
Work_Landline_Number= MAX(CASE WHEN Phone_Usage = 'Work' AND Phone_Type = 'Landline' THEN Phone_Number_Without_Area_Code END)
FROM
OPENJSON ([wd:Personal_Data])
WITH (
Phone_Details NVARCHAR(MAX) '$."wd:Contact_Data"."wd:Phone_Data"' AS JSON
)
CROSS APPLY OPENJSON (Phone_Details)
WITH (
Phone_Number_Without_Area_Code 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"'
)
) ca
- Is there any advantage of using a closed record with a rest descriptor over an open record?
- How is the best way to return an error to ajax request?
- Java: NoClassDefFoundError: org/json/JSONException
- Is there an easy way to reverse pretty print of json in vim?
- Accessing deeply nested fields using DuckDB SQL
- Filter JSON keys with wild card
- Scrape links from many Google searches in Python
- Read Response body and assign into one variable in JMeter
- Generate sample Json output from Json Schema
- JSON schema validation
- How to convert *sql.Rows to typed JSON in Golang
- can't access json proprety
- Inconsistency of character indexes while trying to parse multiple JSON in a file
- Invalid JSON parsing using PHP
- Deserialize JSON objects do different class names
- Flutter and Freezed: Parse a json element from either string or int, "Null check operator used on a null value" on build runner
- What support is there for json schema in Qt / C++ / C
- Getting more then just the first item in JSON array using ChoETL converting JSON to CSV
- JSON to CSV conversion using ChoETL displaying values in one row and not columns
- parsing lxml taken from a url to json
- Jolt Transformation for fetching value of map based on dynamic key
- Query to parse JSON using openrowset converting special characters
- RestTemplate PATCH request
- Convert nested JSON array into separate columns in CSV file
- How to read an external local JSON file in JavaScript?
- How to marshal an ECC public or private key
- Mock and verify request object passed to PostAsJsonAsync
- VM2022:2 Uncaught SyntaxError: Unexpected token : on AJAX call
- How do I deserialize JSON in Rust when combining a flattened component with the need to convert keys?
- How to return JSON objects without explicit aliasing in postgresql