Recently I came across a problem where I had to parse JSON in SQL and retrieve data in a table format.

Parsing Simple JSON in SQL

Let us take the following JSON as an example:

{
    "employeeCode":"E1",
    "emailAddress":"[email protected]",
    "firstName":"nathan",
    "lastName":"poole",
    "jobTitleName":"Developer",
    "userId":"npoole"
}

In SQL you must declare JSON as NVARCHAR(MAX) and select using “OPENJSON” keyword:

declare @JSON NVARCHAR(MAX) = '{
          "employeeCode":"E1",
          "emailAddress":"[email protected]",
          "firstName":"nathan",
          "lastName":"poole",
          "jobTitleName":"Developer",
          "userId":"npoole"
}'

SELECT * FROM OPENJSON(@JSON)

You can then view the data as a key-value store by passing the JSON as a parameter to the OPENJSON method.

key	value	type
employeeCode	E1	1
emailAddress	[email protected]	1
firstName	nathan	1
lastName	poole	1
jobTitleName	Developer	1
userId	npoole	1

If you want to view the same JSON in a column format, you will need to update your query to use WITH clause. Inside the WITH you will need to alias the column name with its type and select the Key attribute from your JSON. Below is the query and its result.

SELECT * FROM OPENJSON(@JSON)
WITH
(
    EmployeeCode NVARCHAR(MAX) '$.employeeCode',
    EmailAddress NVARCHAR(MAX) '$.emailAddress',
    FirstName NVARCHAR(MAX) '$.firstName',
    LastNamr NVARCHAR(MAX) '$.lastName',
    JobTitleName NVARCHAR(MAX) '$.jobTitleName',
    UserID NVARCHAR(MAX) '$.userId'
)

EmployeeCode	EmailAddress	FirstName	LastName	JobTitleName	UserID
E1	[email protected]	nathan	poole	Developer	npoole

Parsing Complex JSON in SQL

Let us expand our JSON and look at a nested JSON example to see how you would expand child elements dynamically.

We will use the following JSON example.

declare @EmployeesJSON NVARCHAR(MAX) = '{
    "Employees": {
      "Leeds": {
        "Zach Ryan": {
          "employeeCode":"E1",
          "emailAddress":"[email protected]",
          "firstName":"zach",
          "lastName":"ryan",
          "jobTitleName":"Developer",
          "userId":"zryan"
        },
        "Buddy Cooper": {
            "employeeCode":"E3",
            "emailAddress":"[email protected]",
            "firstName":"buddy",
            "lastName":"cooper",
            "jobTitleName":"Support",
            "userId":"bcooper"
        },
        "Hana Miller": {
            "employeeCode":"E4",
            "emailAddress":"[email protected]",
            "firstName":"hana",
            "lastName":"miller",
            "jobTitleName":"Developer",
            "userId":"hmiller"
        }
      },
      "Manchester": {
        "Leah Fisher": {
            "employeeCode":"E20",
            "emailAddress":"[email protected]",
            "firstName":"leah",
            "lastName":"fisher",
            "jobTitleName":"Director",
            "userId":"lfisher"
        },
        "Logan Green": {
            "employeeCode":"E21",
            "emailAddress":"[email protected]",
            "firstName":"logan",
            "lastName":"green",
            "jobTitleName":"Developer",
            "userId":"lgreen"
        },
        "Liam Hunt": {
            "employeeCode":"E25",
            "emailAddress":"[email protected]",
            "firstName":"liam",
            "lastName":"hunt",
            "jobTitleName":"Support",
            "userId":"lhunt"
        }
      }
    }
  }'

This JSON has nested elements and if we were to expand this JSON we will get the following result.

SELECT * FROM OPENJSON(@EmployeesJSON)

key	value	type
Employees	{ “Leeds”: { “Zach Ryan”: { “employeeCode”:”E1″, “emailAddress”:”[email protected]”, “firstName”:”zach”, “lastName”:”ryan”, “jobTitleName”:”Developer”, “userId”:”zryan” }, “Buddy Cooper”: { “employeeCode”:”E3″, “emailAddress”:”[email protected]”, “firstName”:”buddy”, “lastName”:”cooper”, “jobTitleName”:”Support”, “userId”:”bcooper” }, “Hana Miller”: { “employeeCode”:”E4″, “emailAddress”:”[email protected]”, “firstName”:”hana”, “lastName”:”miller”, “jobTitleName”:”Developer”, “userId”:”hmiller” } }, “Manchester”: { “Leah Fisher”: { “employeeCode”:”E20″, “emailAddress”:”[email protected]”, “firstName”:”leah”, “lastName”:”fisher”, “jobTitleName”:”Director”, “userId”:”lfisher” }, “Logan Green”: { “employeeCode”:”E21″, “emailAddress”:”[email protected]”, “firstName”:”logan”, “lastName”:”green”, “jobTitleName”:”Developer”, “userId”:”lgreen” }, “Liam Hunt”: { “employeeCode”:”E25″, “emailAddress”:”[email protected]”, “firstName”:”liam”, “lastName”:”hunt”, “jobTitleName”:”Support”, “userId”:”lhunt” } } }	5

All the child elements return as JSON, this is not immediately helpful as we need specific employee details. If you want to get a specific employee detail you can parse to child elements using the following query:

SELECT * FROM OPENJSON(@EmployeesJSON, '$.Employees.Leeds."Hana Miller"')

key	value	type
employeeCode	E4	1
emailAddress	[email protected]	1
firstName	hana	1
lastName	miller	1
jobTitleName	Developer	1
userId	hmiller	1

This is a great way to retrieve child elements. We can make this query more dynamic so we parse the JSON dynamically and retrieve the result in a table format.

In the first step we create a temp table, get the key of JSON as City and its child elements as JSON.

--Use temp tables to parse json
CREATE TABLE #Employees(ID int identity(1,1) primary key, City NVARCHAR(MAX), EmployeeDataJSON NVARCHAR(MAX))

INSERT INTO #Employees (City, EmployeeDataJSON) SELECT [key] AS City, [value] AS EmployeeDataJSON FROM OPENJSON(@EmployeesJSON, '$.Employees')

--Show inserted result
SELECT * FROM #Employees

ID	City	EmployeeDataJSON
1	Leeds	{ “Zach Ryan”: { “employeeCode”:”E1″, “emailAddress”:”[email protected]”, “firstName”:”zach”, “lastName”:”ryan”, “jobTitleName”:”Developer”, “userId”:”zryan” }, “Buddy Cooper”: { “employeeCode”:”E3″, “emailAddress”:”[email protected]”, “firstName”:”buddy”, “lastName”:”cooper”, “jobTitleName”:”Support”, “userId”:”bcooper” }, “Hana Miller”: { “employeeCode”:”E4″, “emailAddress”:”[email protected]”, “firstName”:”hana”, “lastName”:”miller”, “jobTitleName”:”Developer”, “userId”:”hmiller” } }
2	Manchester	{ “Leah Fisher”: { “employeeCode”:”E20″, “emailAddress”:”[email protected]”, “firstName”:”leah”, “lastName”:”fisher”, “jobTitleName”:”Director”, “userId”:”lfisher” }, “Logan Green”: { “employeeCode”:”E21″, “emailAddress”:”[email protected]”, “firstName”:”logan”, “lastName”:”green”, “jobTitleName”:”Developer”, “userId”:”lgreen” }, “Liam Hunt”: { “employeeCode”:”E25″, “emailAddress”:”[email protected]”, “firstName”:”liam”, “lastName”:”hunt”, “jobTitleName”:”Support”, “userId”:”lhunt” } }

In the next step we create another temp table, we loop through the values and insert it into the EmployeesData table. We select the data based on the ID of Employees table and this way we ensure we get the correct city for the employees.

CREATE TABLE #EmployeesData(ID int identity(1,1) primary key, City NVARCHAR(MAX), EmployeeName NVARCHAR(MAX), EmployeeDetailsJSON NVARCHAR(MAX))

--Loop through all of the employees and get their names, save it in a temp table
DECLARE @tableCount int = (SELECT COUNT(*) FROM #Employees)
DECLARE @loopCounter int = 1
WHILE (@loopCounter <= @tableCount)
BEGIN
    INSERT INTO 
        #EmployeesData (City, EmployeeName, EmployeeDetailsJSON) 
        SELECT 
            (SELECT City FROM #Employees WHERE ID = @loopCounter) as City, 
            [key] as EmployeeName, 
            [value] as EmployeeDetailsJSON 
        FROM OPENJSON((SELECT EmployeeDataJSON FROM #Employees WHERE ID = @loopCounter))

    SET @loopCounter = @loopCounter + 1
END

--Show inserted result
SELECT * FROM #EmployeesData

ID	City	EmployeeName	EmployeeDetailsJSON
1	Leeds	Zach Ryan	{ “employeeCode”:”E1″, “emailAddress”:”[email protected]”, “firstName”:”zach”, “lastName”:”ryan”, “jobTitleName”:”Developer”, “userId”:”zryan” }
2	Leeds	Buddy Cooper	{ “employeeCode”:”E3″, “emailAddress”:”[email protected]”, “firstName”:”buddy”, “lastName”:”cooper”, “jobTitleName”:”Support”, “userId”:”bcooper” }
3	Leeds	Hana Miller	{ “employeeCode”:”E4″, “emailAddress”:”[email protected]”, “firstName”:”hana”, “lastName”:”miller”, “jobTitleName”:”Developer”, “userId”:”hmiller” }
4	Manchester	Leah Fisher	{ “employeeCode”:”E20″, “emailAddress”:”[email protected]”, “firstName”:”leah”, “lastName”:”fisher”, “jobTitleName”:”Director”, “userId”:”lfisher” }
5	Manchester	Logan Green	{ “employeeCode”:”E21″, “emailAddress”:”[email protected]”, “firstName”:”logan”, “lastName”:”green”, “jobTitleName”:”Developer”, “userId”:”lgreen” }
6	Manchester	Liam Hunt	{ “employeeCode”:”E25″, “emailAddress”:”[email protected]”, “firstName”:”liam”, “lastName”:”hunt”, “jobTitleName”:”Support”, “userId”:”lhunt” }

In the last step we follow the same concept as shown before and this way we have expanded a nested JSON into a table format that you can use.

CREATE TABLE #EmployeesDetails(ID int identity(1,1) primary key, City NVARCHAR(MAX), EmployeeName NVARCHAR(MAX), EmployeeCode NVARCHAR(MAX), EmailAddress NVARCHAR(MAX), FirstName NVARCHAR(MAX), LastName NVARCHAR(MAX), JobTitleName NVARCHAR(MAX), UserID NVARCHAR(MAX))

--Loop through all of the EmployeesData and get their city and names, save it in a temp table
DECLARE @employeeDataTableCount int = (SELECT COUNT(*) FROM #EmployeesData)
DECLARE @employeeDataloopCounter int = 1
WHILE (@employeeDataloopCounter <= @employeeDataTableCount)
BEGIN
    INSERT INTO 
        #EmployeesDetails (City, EmployeeName, EmployeeCode, EmailAddress, FirstName, LastName, JobTitleName, UserID) 
        SELECT 
            City, 
            EmployeeName,
            (SELECT * FROM OPENJSON(EmployeeDetailsJSON)
            WITH
            (
                EmployeeCode NVARCHAR(MAX) '$.employeeCode'
            )) AS EmployeeCode,
            (SELECT * FROM OPENJSON(EmployeeDetailsJSON)
            WITH
            (
                EmailAddress NVARCHAR(MAX) '$.emailAddress'
            )) AS EmailAddress,
            (SELECT * FROM OPENJSON(EmployeeDetailsJSON)
            WITH
            (
                FirstName NVARCHAR(MAX) '$.firstName'
            )) AS FirstName,
            (SELECT * FROM OPENJSON(EmployeeDetailsJSON)
            WITH
            (
                LastName NVARCHAR(MAX) '$.lastName'
            )) AS LastName,
            (SELECT * FROM OPENJSON(EmployeeDetailsJSON)
            WITH
            (
                JobTitleName NVARCHAR(MAX) '$.jobTitleName'
            )) AS JobTitleName, 
            (SELECT * FROM OPENJSON(EmployeeDetailsJSON)
            WITH
            (
                UserID NVARCHAR(MAX) '$.userId'
            )) AS UserID
        FROM #EmployeesData WHERE ID = @employeeDataloopCounter

    SET @employeeDataloopCounter = @employeeDataloopCounter + 1
END

--Show inserted result
SELECT * FROM #EmployeesDetails

ID	City	EmployeeName	EmployeeCode	EmailAddress	FirstName	LastName	JobTitleName	UserID
1	Leeds	Zach Ryan	E1	[email protected]	zach	ryan	Developer	zryan
2	Leeds	Buddy Cooper	E3	[email protected]	buddy	cooper	Support	bcooper
3	Leeds	Hana Miller	E4	[email protected]	hana	miller	Developer	hmiller
4	Manchester	Leah Fisher	E20	[email protected]	leah	fisher	Director	lfisher
5	Manchester	Logan Green	E21	[email protected]	logan	green	Developer	lgreen
6	Manchester	Liam Hunt	E25	[email protected]	liam	hunt	Support	lhunt

Using temporary tables and a “WITH” clause we can expand a nested JSON and display the result in a table format.

Below is the full script used to parse complex JSON.

--Select nested json as key value
SELECT * FROM OPENJSON(@EmployeesJSON)

--Parse nested json by going to child level
SELECT * FROM OPENJSON(@EmployeesJSON, '$.Employees.Leeds."Hana Miller"')

--Use temp tables to parse json
CREATE TABLE #Employees(ID int identity(1,1) primary key, City NVARCHAR(MAX), EmployeeDataJSON NVARCHAR(MAX))

INSERT INTO #Employees (City, EmployeeDataJSON) SELECT [key] AS City, [value] AS EmployeeDataJSON FROM OPENJSON(@EmployeesJSON, '$.Employees')

--Show inserted result
SELECT * FROM #Employees

CREATE TABLE #EmployeesData(ID int identity(1,1) primary key, City NVARCHAR(MAX), EmployeeName NVARCHAR(MAX), EmployeeDetailsJSON NVARCHAR(MAX))

--Loop through all of the employees and get their names, save it in a temp table
DECLARE @tableCount int = (SELECT COUNT(*) FROM #Employees)
DECLARE @loopCounter int = 1
WHILE (@loopCounter <= @tableCount)
BEGIN
    INSERT INTO 
        #EmployeesData (City, EmployeeName, EmployeeDetailsJSON) 
        SELECT 
            (SELECT City FROM #Employees WHERE ID = @loopCounter) as City, 
            [key] as EmployeeName, 
            [value] as EmployeeDetailsJSON 
        FROM OPENJSON((SELECT EmployeeDataJSON FROM #Employees WHERE ID = @loopCounter))

    SET @loopCounter = @loopCounter + 1
END

--Show inserted result
SELECT * FROM #EmployeesData

CREATE TABLE #EmployeesDetails(ID int identity(1,1) primary key, City NVARCHAR(MAX), EmployeeName NVARCHAR(MAX), EmployeeCode NVARCHAR(MAX), EmailAddress NVARCHAR(MAX), FirstName NVARCHAR(MAX), LastName NVARCHAR(MAX), JobTitleName NVARCHAR(MAX), UserID NVARCHAR(MAX))

--Loop through all of the EmployeesData and get their city and names, save it in a temp table
DECLARE @employeeDataTableCount int = (SELECT COUNT(*) FROM #EmployeesData)
DECLARE @employeeDataloopCounter int = 1
WHILE (@employeeDataloopCounter <= @employeeDataTableCount)
BEGIN
    INSERT INTO 
        #EmployeesDetails (City, EmployeeName, EmployeeCode, EmailAddress, FirstName, LastName, JobTitleName, UserID) 
        SELECT 
            City, 
            EmployeeName,
            (SELECT * FROM OPENJSON(EmployeeDetailsJSON)
            WITH
            (
                EmployeeCode NVARCHAR(MAX) '$.employeeCode'
            )) AS EmployeeCode,
            (SELECT * FROM OPENJSON(EmployeeDetailsJSON)
            WITH
            (
                EmailAddress NVARCHAR(MAX) '$.emailAddress'
            )) AS EmailAddress,
            (SELECT * FROM OPENJSON(EmployeeDetailsJSON)
            WITH
            (
                FirstName NVARCHAR(MAX) '$.firstName'
            )) AS FirstName,
            (SELECT * FROM OPENJSON(EmployeeDetailsJSON)
            WITH
            (
                LastName NVARCHAR(MAX) '$.lastName'
            )) AS LastName,
            (SELECT * FROM OPENJSON(EmployeeDetailsJSON)
            WITH
            (
                JobTitleName NVARCHAR(MAX) '$.jobTitleName'
            )) AS JobTitleName, 
            (SELECT * FROM OPENJSON(EmployeeDetailsJSON)
            WITH
            (
                UserID NVARCHAR(MAX) '$.userId'
            )) AS UserID
        FROM #EmployeesData WHERE ID = @employeeDataloopCounter

    SET @employeeDataloopCounter = @employeeDataloopCounter + 1
END

--Show inserted result
SELECT * FROM #EmployeesDetails


--CleanUp
DROP TABLE #Employees
DROP TABLE #EmployeesData
DROP TABLE #EmployeesDetails

Parse JSON Data with OPENJSON in SQL Server

Author: Jamie Watson

Related posts