Question posted in Json
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How can I get different values from a JSON array?

OJSlott
May 9, 2023
326 views
1 vote
2 Answers

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'

Answers

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

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

Please signup or login to give your own answer.

Click here to cancel reply.