Get different values from JSON array

Question

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'

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, May 08 '23 at 17:43

score 1 · Accepted Answer · edited May 08 '23 at 19:30

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

Yitzhak Khabinsky · Answer 2 · 2023-05-08T18:30:46.240

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, jdata NVARCHAR(MAX));
INSERT INTO @tbl (jdata) VALUES
(N'[{
    "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"
            }
        ]
    }
}]');
-- DDL and sample data population, end

;WITH rs AS
(
SELECT ID AS Worker_ID
    , [Phone_Number]   
    , Phone_Type 
FROM @tbl
CROSS APPLY OPENJSON (jdata)
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"'
)
WHERE Phone_Usage = 'Work'
)
SELECT DISTINCT Worker_ID 
    , MAX(IIF(Phone_Type = 'Mobile', Phone_Number, '')) OVER (PARTITION BY Worker_ID) AS Mobile
    , MAX(IIF(Phone_Type = 'Landline', Phone_Number, '')) OVER (PARTITION BY Worker_ID) AS Landline
FROM rs
GROUP BY Worker_ID, Phone_Type, Phone_Number;

Output

Worker_ID	Mobile	Landline
1	87654321	12341234

This will be less efficient than my answer - firstly using a windowed aggregate and getting rid of the duplicates with `DISTINCT` is not efficient and secondly there is no need to aggregate across the whole result set and include a `GROUP BY Worker_ID` when you can just aggregate each document individually — Martin Smith, May 08 '23 at 18:51
Thank you guys. This is exactly what I was looking for. I'll be using @MartinSmith solution since this is the most simple and does the job. — OJ Slott, May 09 '23 at 04:57

Get different values from JSON array

2 Answers2