SQL Server 2019 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE EmployeeData
(
    EmployeeId INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    EmployeeJSON NVARCHAR(MAX),
    Comments NVARCHAR(100)
);

-- Insert 5 records with complex JSON data
INSERT INTO EmployeeData
(
    EmployeeId,
    EmployeeName,
    EmployeeJSON,
    Comments
)
VALUES
(1, 'John Doe',
 N'{
        "EmployeeId": 1,
        "EmployeeName": "John Doe",
        "$Salary": 60000,
        "HireDate": "2020-01-15",
        "Contact Info": {
            "Email": "john.doe@example.com"
        }
    }', 'Missing phone, skills, and addresses.'),
(2, 'Jane Smith',
 N'{
        "EmployeeId": 2,
        "EmployeeName": "Jane Smith",
        "$Salary": 55000,
        "HireDate": "2021-02-10",
        "Contact Info": {
            "Phone": "555-9876"
        },
        "Skills": ["JavaScript", "HTML", "CSS"],
        "Addresses": [
            {
                "Type": "Home",
                "Street": "789 Elm St",
                "City": "Townsville",
                "State": "TX",
                "PostalCode": "67890"
            }
        ]
    }', 'Missing Email.'),

-- Insert more records here...
(3, 'Michael Johnson',
 N'{
        "EmployeeId": 3,
        "EmployeeName": "Michael Johnson",
        "$Salary": 75000,
        "HireDate": "2019-06-20",
        "Contact Info": {
            "Email": "michael.johnson@example.com",
            "Phone": "555-5555"
        },
        "Skills": ["C#", ".NET", "ASP.NET"],
        "Addresses": [
            {
                "Type": "Home",
                "Street": "456 Oak Ave",
                "City": "Villagetown",
                "State": "FL",
                "PostalCode": "54321"
            },
            {
                "Type": "Work",
                "Street": "789 Office St",
                "City": "Worksville",
                "State": "CA",
                "PostalCode": "12345"
            }
        ]
    }', 'Employee info complete.'),
(4, 'Emily Brown',
 N'{
        "EmployeeId": 4,
        "EmployeeName": "Emily Brown",
        "$Salary": 50000,
        "HireDate": "2022-04-05",
        "Contact Info": {
            "Email": "emily.brown@example.com",
            "Phone": "555-8888"
        },
        "Skills": ["Python", "Data Analysis"],
        "Addresses": [
            {
                "Type": "Home",
                "Street": "987 Maple Rd",
                "City": "Mapletown",
                "State": "IL",
                "PostalCode": "45678"
            }
        ]
    }', 'Employee info complete.'),
(5, 'David Wilson',
 N'{
        "EmployeeId": 5,
        "EmployeeName": "David Wilson",
        "$Salary": 80000,
        "HireDate": "2020-09-28",
        "Contact Info": {
            "Email": "david.wilson@example.com",
            "Phone": "555-7777"
        },
        "Skills": ["Java", "Spring Framework", "REST APIs"],
        "Addresses": [
            {
                "Type": "Home",
                "Street": "654 Pine Ln",
                "City": "Pinetown",
                "State": "NC",
                "PostalCode": "23456"
            },
            {
                "Type": "Work",
                "Street": "876 Office Ave",
                "City": "Officetown",
                "State": "WA",
                "PostalCode": "34567"
            }
        ]
    }', 'Employee info complete.');
5 rows affected

SELECT * FROM dbo.EmployeeData


EmployeeId
EmployeeName
EmployeeJSON
Comments


1
John Doe
{
        "EmployeeId": 1,
        "EmployeeName": "John Doe",
        "$Salary": 60000,
        "HireDate": "2020-01-15",
        "Contact Info": {
            "Email": "john.doe@example.com"
        }
    }
Missing phone, skills, and addresses.

2
Jane Smith
{
        "EmployeeId": 2,
        "EmployeeName": "Jane Smith",
        "$Salary": 55000,
        "HireDate": "2021-02-10",
        "Contact Info": {
            "Phone": "555-9876"
        },
        "Skills": ["JavaScript", "HTML", "CSS"],
        "Addresses": [
            {
                "Type": "Home",
                "Street": "789 Elm St",
                "City": "Townsville",
                "State": "TX",
                "PostalCode": "67890"
            }
        ]
    }
Missing Email.

3
Michael Johnson
{
        "EmployeeId": 3,
        "EmployeeName": "Michael Johnson",
        "$Salary": 75000,
        "HireDate": "2019-06-20",
        "Contact Info": {
            "Email": "michael.johnson@example.com",
            "Phone": "555-5555"
        },
        "Skills": ["C#", ".NET", "ASP.NET"],
        "Addresses": [
            {
                "Type": "Home",
                "Street": "456 Oak Ave",
                "City": "Villagetown",
                "State": "FL",
                "PostalCode": "54321"
            },
            {
                "Type": "Work",
                "Street": "789 Office St",
                "City": "Worksville",
                "State": "CA",
                "PostalCode": "12345"
            }
        ]
    }
Employee info complete.

4
Emily Brown
{
        "EmployeeId": 4,
        "EmployeeName": "Emily Brown",
        "$Salary": 50000,
        "HireDate": "2022-04-05",
        "Contact Info": {
            "Email": "emily.brown@example.com",
            "Phone": "555-8888"
        },
        "Skills": ["Python", "Data Analysis"],
        "Addresses": [
            {
                "Type": "Home",
                "Street": "987 Maple Rd",
                "City": "Mapletown",
                "State": "IL",
                "PostalCode": "45678"
            }
        ]
    }
Employee info complete.

5
David Wilson
{
        "EmployeeId": 5,
        "EmployeeName": "David Wilson",
        "$Salary": 80000,
        "HireDate": "2020-09-28",
        "Contact Info": {
            "Email": "david.wilson@example.com",
            "Phone": "555-7777"
        },
        "Skills": ["Java", "Spring Framework", "REST APIs"],
        "Addresses": [
            {
                "Type": "Home",
                "Street": "654 Pine Ln",
                "City": "Pinetown",
                "State": "NC",
                "PostalCode": "23456"
            },
            {
                "Type": "Work",
                "Street": "876 Office Ave",
                "City": "Officetown",
                "State": "WA",
                "PostalCode": "34567"
            }
        ]
    }
Employee info complete.


SELECT EA.EmployeeId,
       EA.EmployeeName,
       EA.Comments,
       ROOT_JS.Salary,
       ROOT_JS.DateHired
FROM dbo.EmployeeData AS EA
    CROSS APPLY
    OPENJSON(EA.EmployeeJSON)
    WITH
    (
        EmployeeId INT '$.EmployeeId',
        EmployeeName VARCHAR(100) '$.EmployeeName',
        Salary INT '$."$Salary"',
        DateHired DATE '$.HireDate',
        ContactInfo NVARCHAR(MAX) '$."Contact Info"' AS JSON,
        Skills NVARCHAR(MAX) '$.Skills' AS JSON,
        Addresses NVARCHAR(MAX) '$.Addresses' AS JSON
    ) AS ROOT_JS


EmployeeId
EmployeeName
Comments
Salary
DateHired


1
John Doe
Missing phone, skills, and addresses.
60000
2020-01-15

2
Jane Smith
Missing Email.
55000
2021-02-10

3
Michael Johnson
Employee info complete.
75000
2019-06-20

4
Emily Brown
Employee info complete.
50000
2022-04-05

5
David Wilson
Employee info complete.
80000
2020-09-28


SELECT EA.EmployeeId,
       EA.EmployeeName,
       EA.Comments,
       ROOT_JS.Salary,
       ROOT_JS.DateHired,
       CONTACT.Email,
       CONTACT.Phone
FROM dbo.EmployeeData AS EA
    CROSS APPLY
    OPENJSON(EA.EmployeeJSON)
    WITH
    (
        EmployeeId INT '$.EmployeeId',
        EmployeeName VARCHAR(100) '$.EmployeeName',
        Salary INT '$."$Salary"',
        DateHired DATE '$.HireDate',
        ContactInfo NVARCHAR(MAX) '$."Contact Info"' AS JSON,
        Skills NVARCHAR(MAX) '$.Skills' AS JSON,
        Addresses NVARCHAR(MAX) '$.Addresses' AS JSON
    ) AS ROOT_JS
    OUTER APPLY
(
    SELECT *
    FROM
        OPENJSON(ROOT_JS.ContactInfo)
        WITH
        (
            Email VARCHAR(50) '$.Email',
            Phone VARCHAR(50) '$.Phone'
        )
) AS CONTACT


EmployeeId
EmployeeName
Comments
Salary
DateHired
Email
Phone


1
John Doe
Missing phone, skills, and addresses.
60000
2020-01-15
john.doe@example.com
null

2
Jane Smith
Missing Email.
55000
2021-02-10
null
555-9876

3
Michael Johnson
Employee info complete.
75000
2019-06-20
michael.johnson@example.com
555-5555

4
Emily Brown
Employee info complete.
50000
2022-04-05
emily.brown@example.com
555-8888

5
David Wilson
Employee info complete.
80000
2020-09-28
david.wilson@example.com
555-7777


SELECT EA.EmployeeId,
       EA.EmployeeName,
       EA.Comments,
       ROOT_JS.Salary,
       ROOT_JS.DateHired,
       CONTACT.Email,
       CONTACT.Phone,
       SKILLS.SkillCount,
       SKILLS.SkillList
FROM dbo.EmployeeData AS EA
    CROSS APPLY
    OPENJSON(EA.EmployeeJSON)
    WITH
    (
        EmployeeId INT '$.EmployeeId',
        EmployeeName VARCHAR(100) '$.EmployeeName',
        Salary INT '$."$Salary"',
        DateHired DATE '$.HireDate',
        ContactInfo NVARCHAR(MAX) '$."Contact Info"' AS JSON,
        Skills NVARCHAR(MAX) '$.Skills' AS JSON,
        Addresses NVARCHAR(MAX) '$.Addresses' AS JSON
    ) AS ROOT_JS
    OUTER APPLY
(
    SELECT *
    FROM
        OPENJSON(ROOT_JS.ContactInfo)
        WITH
        (
            Email VARCHAR(50) '$.Email',
            Phone VARCHAR(50) '$.Phone'
        )
) AS CONTACT
    OUTER APPLY
(
    SELECT COUNT(*) AS SkillCount,
           STRING_AGG(Skill, ', ')WITHIN GROUP(ORDER BY Skill) AS SkillList
    FROM
        OPENJSON(ROOT_JS.Skills)
        WITH
        (
            Skill VARCHAR(50) '$'
        )
) AS SKILLS


EmployeeId
EmployeeName
Comments
Salary
DateHired
Email
Phone
SkillCount
SkillList


1
John Doe
Missing phone, skills, and addresses.
60000
2020-01-15
john.doe@example.com
null
0
null

3
Michael Johnson
Employee info complete.
75000
2019-06-20
michael.johnson@example.com
555-5555
3
.NET, ASP.NET, C#

5
David Wilson
Employee info complete.
80000
2020-09-28
david.wilson@example.com
555-7777
3
Java, REST APIs, Spring Framework

2
Jane Smith
Missing Email.
55000
2021-02-10
null
555-9876
3
CSS, HTML, JavaScript

4
Emily Brown
Employee info complete.
50000
2022-04-05
emily.brown@example.com
555-8888
2
Data Analysis, Python


SELECT EA.EmployeeId,
       EA.EmployeeName,
       EA.Comments,
       ROOT_JS.Salary,
       ROOT_JS.DateHired,
       CONTACT.Email,
       CONTACT.Phone,
       SKILLS.SkillCount,
       SKILLS.SkillList,
       ADDRESSES.AddressCount,
       ADDRESSES.AddressList
FROM dbo.EmployeeData AS EA
    CROSS APPLY
    OPENJSON(EA.EmployeeJSON)
    WITH
    (
        EmployeeId INT '$.EmployeeId',
        EmployeeName VARCHAR(100) '$.EmployeeName',
        Salary INT '$."$Salary"',
        DateHired DATE '$.HireDate',
        ContactInfo NVARCHAR(MAX) '$."Contact Info"' AS JSON,
        Skills NVARCHAR(MAX) '$.Skills' AS JSON,
        Addresses NVARCHAR(MAX) '$.Addresses' AS JSON
    ) AS ROOT_JS
    OUTER APPLY
(
    SELECT *
    FROM
        OPENJSON(ROOT_JS.ContactInfo)
        WITH
        (
            Email VARCHAR(50) '$.Email',
            Phone VARCHAR(50) '$.Phone'
        )
) AS CONTACT
    OUTER APPLY
(
    SELECT COUNT(*) AS SkillCount,
           STRING_AGG(Skill, ', ')WITHIN GROUP(ORDER BY Skill) AS SkillList
    FROM
        OPENJSON(ROOT_JS.Skills)
        WITH
        (
            Skill VARCHAR(50) '$'
        )
) AS SKILLS
    OUTER APPLY
(
    SELECT COUNT(*) AS AddressCount,
           STRING_AGG(CONCAT_WS(' ', AddressType + ':', Street, City, State, PostalCode), ' | ')WITHIN GROUP(ORDER BY AddressType DESC) AS AddressList
    FROM
        OPENJSON(ROOT_JS.Addresses)
        WITH
        (
            AddressType VARCHAR(10) '$.Type',
            Street VARCHAR(10) '$.Street',
            City VARCHAR(10) '$.City',
            State VARCHAR(10) '$.State',
            PostalCode VARCHAR(10) '$.PostalCode'
        )
) AS ADDRESSES
ORDER BY EA.EmployeeId


EmployeeId
EmployeeName
Comments
Salary
DateHired
Email
Phone
SkillCount
SkillList
AddressCount
AddressList


1
John Doe
Missing phone, skills, and addresses.
60000
2020-01-15
john.doe@example.com
null
0
null
0
null

2
Jane Smith
Missing Email.
55000
2021-02-10
null
555-9876
3
CSS, HTML, JavaScript
1
Home: 789 Elm St Townsville TX 67890

3
Michael Johnson
Employee info complete.
75000
2019-06-20
michael.johnson@example.com
555-5555
3
.NET, ASP.NET, C#
2
Work: 789 Office Worksville CA 12345 | Home: 456 Oak Av Villagetow FL 54321

4
Emily Brown
Employee info complete.
50000
2022-04-05
emily.brown@example.com
555-8888
2
Data Analysis, Python
1
Home: 987 Maple  Mapletown IL 45678

5
David Wilson
Employee info complete.
80000
2020-09-28
david.wilson@example.com
555-7777
3
Java, REST APIs, Spring Framework
2
Work: 876 Office Officetown WA 34567 | Home: 654 Pine L Pinetown NC 23456

EmployeeId	EmployeeName	EmployeeJSON	Comments
1	John Doe	{ "EmployeeId": 1, "EmployeeName": "John Doe", "$Salary": 60000, "HireDate": "2020-01-15", "Contact Info": { "Email": "john.doe@example.com" } }	Missing phone, skills, and addresses.
2	Jane Smith	{ "EmployeeId": 2, "EmployeeName": "Jane Smith", "$Salary": 55000, "HireDate": "2021-02-10", "Contact Info": { "Phone": "555-9876" }, "Skills": ["JavaScript", "HTML", "CSS"], "Addresses": [ { "Type": "Home", "Street": "789 Elm St", "City": "Townsville", "State": "TX", "PostalCode": "67890" } ] }	Missing Email.
3	Michael Johnson	{ "EmployeeId": 3, "EmployeeName": "Michael Johnson", "$Salary": 75000, "HireDate": "2019-06-20", "Contact Info": { "Email": "michael.johnson@example.com", "Phone": "555-5555" }, "Skills": ["C#", ".NET", "ASP.NET"], "Addresses": [ { "Type": "Home", "Street": "456 Oak Ave", "City": "Villagetown", "State": "FL", "PostalCode": "54321" }, { "Type": "Work", "Street": "789 Office St", "City": "Worksville", "State": "CA", "PostalCode": "12345" } ] }	Employee info complete.
4	Emily Brown	{ "EmployeeId": 4, "EmployeeName": "Emily Brown", "$Salary": 50000, "HireDate": "2022-04-05", "Contact Info": { "Email": "emily.brown@example.com", "Phone": "555-8888" }, "Skills": ["Python", "Data Analysis"], "Addresses": [ { "Type": "Home", "Street": "987 Maple Rd", "City": "Mapletown", "State": "IL", "PostalCode": "45678" } ] }	Employee info complete.
5	David Wilson	{ "EmployeeId": 5, "EmployeeName": "David Wilson", "$Salary": 80000, "HireDate": "2020-09-28", "Contact Info": { "Email": "david.wilson@example.com", "Phone": "555-7777" }, "Skills": ["Java", "Spring Framework", "REST APIs"], "Addresses": [ { "Type": "Home", "Street": "654 Pine Ln", "City": "Pinetown", "State": "NC", "PostalCode": "23456" }, { "Type": "Work", "Street": "876 Office Ave", "City": "Officetown", "State": "WA", "PostalCode": "34567" } ] }	Employee info complete.

EmployeeId	EmployeeName	Comments	Salary	DateHired
1	John Doe	Missing phone, skills, and addresses.	60000	2020-01-15
2	Jane Smith	Missing Email.	55000	2021-02-10
3	Michael Johnson	Employee info complete.	75000	2019-06-20
4	Emily Brown	Employee info complete.	50000	2022-04-05
5	David Wilson	Employee info complete.	80000	2020-09-28