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
- Trying to decode a JSON value that can be a String or Int
- Problem reading the returned JSON into PHP usable arrays in a foreach loop
- Using JQ query how to update propery inside two nested arrays?
- Swift4: UIImage from URL
- How can I create a JSON in R to match this schema?
- Can't load images from local files in React, React is trying to pull image from proxy?
- How to connect Javascript to Python sharing data with JSON format in both ways?
- Converting csv to json with jq
- Watson Assistant Integration with Phone Using Twilio
- Parse json in javascript - long numbers get rounded
- Obtaining information from a list/dict in Python
- Ignore Null Values When Serializing JSON
- How to build expression in ADF from json using parameterized variable?
- Laravel API stopped responding. How to return to service?
- JQ Cannot iterate over null
- Newtonsoft Object serialized to String. JObject instance expected
- Combine JSON objects from multiple rows into a single object
- Nested Json to pandas DataFrame with specific format
- Defining a HTML template to append using jQuery
- unbale to get the data from ORDS plsql block if input is an json array of objects
- How to get nested data in Gmail API and JSON with Python
- Convert from JSON object to expando object in c#
- Generate JSON from R data, tidyverse style
- How to read json file from assests in android using Kotlin?
- python jsonify dictionary in utf-8
- Replace empty string with null via JOLT SPEC
- polars (python-api): read_json fails to parse date
- How to get access the last item of a JSON array using Newtonsoft.Json SelectToken method
- Json server update
- Retrieve company name with ticker symbol input, yahoo or google API