Problem
When working with SQL Server, sooner or later you’ll need to deal with JSON. APIs, log files, and even some third-party integrations send data in JSON format. The challenge is that JSON doesn’t map directly to tables. Especially when the data includes nested objects or arrays.
Because of this, querying or joining your current relational data becomes challenging. How can we convert this adaptable format into structured rows and columns that SQL Server can use?
Solution
The OPENJSON function, which was introduced in SQL Server 2016, greatly simplifies this task. You can make a reusable tool that takes JSON input and puts it into a table by enclosing it in a stored procedure. This method maintains the consistency and cleanliness of your ETL process. In the next examples, I’ll walk through flat JSON, nested objects, and arrays, and show how to turn each into proper SQL rows.
You’ll learn the SQL syntax for each case and understand the reasoning behind parsing JSON. Additionally, you will learn best practices to guarantee the consistency and dependability of your data. Before attempting these examples, make sure your SQL Server version (2016 or later) supports OPENJSON.
Create Test Table
We’ll use a sample table to hold employee data for our examples:
--MSSQLTips.com (SQL)
CREATE TABLE Employees (
Id INT,
Name NVARCHAR(100),
Department NVARCHAR(100),
Salary DECIMAL(10, 2)
);Flat JSON Array INSERT into SQL Server Table
Flat JSON, the most popular and straightforward JSON format, is made up of an array of objects with simple key-value pairs and no nesting. This format is perfect for straightforward data imports because it aligns naturally with relational database tables.
A sample JSON array representing an employee list can be found below. Id, Name, Department, and Salary are the fundamental properties of every object.
--MSSQLTips.com (JSON)
[
{ "Id": 1, "Name": "Alice", "Department": "HR", "Salary": 5000 },
{ "Id": 2, "Name": "Bob", "Department": "IT", "Salary": 6000 }
]Stored Procedure to Insert Flat JSON
We developed the following stored procedure to import a JSON string into SQL Server. It uses the OPENJSON function to parse the input, while the WITH (…) clause maps each JSON property directly to the appropriate SQL Server column and data type.
--MSSQLTips.com (SQL)
CREATE PROCEDURE InsertFlatJSON
@json NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Employees (Id, Name, Department, Salary)
SELECT
Id, Name, Department, Salary
FROM OPENJSON(@json)
WITH (
Id INT,
Name NVARCHAR(100),
Department NVARCHAR(100),
Salary DECIMAL(10, 2)
);
ENDHere, we declare a variable @json containing the same array of employee objects, then call the procedure. This simulates how you might pass JSON data from an application or API into SQL Server.
--MSSQLTips.com (SQL)
DECLARE @json NVARCHAR(MAX) = N'
[
{ "Id": 1, "Name": "Alice", "Department": "HR", "Salary": 5000 },
{ "Id": 2, "Name": "Bob", "Department": "IT", "Salary": 6000 }
]';
EXEC InsertFlatJSON @json;After executing the code, the rows are inserted directly into the Employees table.
Nested JSON Array Insert Into SQL Table
Nested JSON often appears when data is structured hierarchically under a parent object or comes from more complex APIs. While this format is more sophisticated, it also introduces extra parsing challenges in SQL Server.
In this case, each employee comes inside an “Employee” encapsulating object. To derive and place data into a relational table, we need to first get the nested JSON, and then interpret its properties.
The JSON below contains an array where each item includes an “Employee” object with details inside. This is typical of API responses that organize data under logical keys:
--MSSQLTips.com (JSON)
[
{
"Employee": {
"Id": 3,
"Name": "Charlie",
"Department": "Finance",
"Salary": 7000
}
}
]Stored Procedure to Insert Nested JSON
Two levels of OPENJSON are used to work with this structure: the first level extracts the “Employee” block as a raw JSON string, and the second level uses CROSS APPLY to split that JSON into distinct columns according to path expressions.
--MSSQLTips.com (SQL)
CREATE PROCEDURE InsertNestedJSON
@json NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Employees (Id, Name, Department, Salary)
SELECT
Employee_Id,
Employee_Name,
Employee_Department,
Employee_Salary
FROM OPENJSON(@json)
WITH (
Employee NVARCHAR(MAX) AS JSON
)
CROSS APPLY OPENJSON(Employee)
WITH (
Employee_Id INT '$.Id',
Employee_Name NVARCHAR(100) '$.Name',
Employee_Department NVARCHAR(100) '$.Department',
Employee_Salary DECIMAL(10, 2) '$.Salary'
);
ENDThe outer OPENJSON extracts each “Employee” object. The AS JSON modifier treats the value as a nested JSON document. The CROSS APPLY opens and flattens the nested structure into columns.
We now pass the nested JSON string to the procedure. This simulates how structured data might be passed from middleware or microservices:
--MSSQLTips.com (SQL)
DECLARE @json NVARCHAR(MAX) = N'
[
{
"Employee": {
"Id": 3,
"Name": "Charlie",
"Department": "Finance",
"Salary": 7000
}
}
]';
EXEC InsertNestedJSON @json;The nested employee data is flattened and inserted into the Employees table. This shows how nested JSON data can be flattened through multiple levels of OPENJSON, using path-based extraction to retain control over the data pipeline.
JSON with Arrays Inside Objects
In a lot of practical cases, JSON documents are not limited to a combination of nested and flat structures. Most of the time, they contain arrays within objects. This is often the case when a department or other parent entity contains multiple related records, like employees.
The method for extracting shared parent-level values, such as Department, and assigning them to each child element in an embedded array is shown in this example.
A JSON object describing a department and containing an array of its employees can be found below. APIs that return hierarchical organizational data frequently follow this pattern.
--MSSQLTips.com (JSON)
{
"Department": "Engineering",
"Employees": [
{ "Id": 4, "Name": "David", "Salary": 5500 },
{ "Id": 5, "Name": "Eva", "Salary": 5800 }
]
}Stored Procedure to Insert JSON with Arrays
While the employees are listed in an array of objects, each with its own ID, name, and salary, the department only appears once in this JSON as a top-level value. Working with this structure involves extracting the department name first, then going through the employee array and appending that department to each record.
--MSSQLTips.com (SQL)
CREATE PROCEDURE InsertFromArrayJSON
@json NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @department NVARCHAR(100);
SELECT @department = Department
FROM OPENJSON(@json)
WITH (Department NVARCHAR(100))
INSERT INTO Employees (Id, Name, Department, Salary)
SELECT
Id,
Name,
@department AS Department,
Salary
FROM OPENJSON(@json, '$.Employees')
WITH (
Id INT,
Name NVARCHAR(100),
Salary DECIMAL(10, 2)
);
ENDThe department is extracted as a stand-alone value by OPENJSON(@json). OPENJSON(@json, ‘$.Employees’) uses a JSON path to parse the array of employees. A parent-child link is created in the SQL data since every row in the result receives the same @department value.
We define the same JSON object in a SQL variable and call the procedure to simulate ingestion:
--MSSQLTips.com (SQL)
DECLARE @json NVARCHAR(MAX) = N'
{
"Department": "Engineering",
"Employees": [
{ "Id": 4, "Name": "David", "Salary": 5500 },
{ "Id": 5, "Name": "Eva", "Salary": 5800 }
]
}';
EXEC InsertFromArrayJSON @json;Both David and Eva are inserted with the “Engineering” department. This example showcases how SQL Server can parse arrays inside objects and how values from different levels of a JSON document can be combined during insertion.
Hybrid JSON – Mix of Nested and Flat Arrays
JSON data frequently appears in hybrid formats that combine flat fields (like department name) and nested arrays (like employee lists) in many enterprise applications, particularly in HR, CRM, and ERP systems. One of the most intricate and realistic data structures you will come across is this one.
This example demonstrates how to manage a repeating array of JSON objects, where each object has a nested array, like “Employees,” that contains several items, as well as a flat value, like “Department”.
Let’s look at a JSON structure containing multiple department blocks. Each department includes a name and an array of employee objects. This is very common when APIs return batched data by category or grouping.
--MSSQLTips.com (JSON)
[
{
"Department": "Marketing",
"Employees": [
{ "Id": 6, "Name": "Frank", "Salary": 6200 },
{ "Id": 7, "Name": "Grace", "Salary": 6300 }
]
},
{
"Department": "Sales",
"Employees": [
{ "Id": 8, "Name": "Hannah", "Salary": 6400 }
]
}
]Every element in the array has a child array of “Employees” and represents a department object.
Stored Procedure to Insert Hybrid JSON
The procedure below involves using a cursor to loop through the top-level array (departments) and then extracting and parsing the nested “Employees” array for each department.
--MSSQLTips.com (SQL)
CREATE PROCEDURE InsertHybridJSON
@json NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @department NVARCHAR(100), @employees NVARCHAR(MAX)
DECLARE departments_cursor CURSOR FOR
SELECT Department, Employees
FROM OPENJSON(@json)
WITH (
Department NVARCHAR(100),
Employees NVARCHAR(MAX) AS JSON
);
OPEN departments_cursor;
FETCH NEXT FROM departments_cursor INTO @department, @employees;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO Employees (Id, Name, Department, Salary)
SELECT
Id,
Name,
@department,
Salary
FROM OPENJSON(@employees)
WITH (
Id INT,
Name NVARCHAR(100),
Salary DECIMAL(10,2)
);
FETCH NEXT FROM departments_cursor INTO @department, @employees;
END
CLOSE departments_cursor;
DEALLOCATE departments_cursor;
ENDThe outer OPENJSON extracts each department record and its corresponding “Employees” array. The cursor allows sequential access to each department block. The nested OPENJSON(@employees) parses each employee entry under its associated department context. The @department value is carried over into the INSERT operation to maintain relational integrity.
Now let’s call the procedure with our sample hybrid JSON:
--MSSQLTips.com (SQL)
DECLARE @json NVARCHAR(MAX) = N'
[
{
"Department": "Marketing",
"Employees": [
{ "Id": 6, "Name": "Frank", "Salary": 6200 },
{ "Id": 7, "Name": "Grace", "Salary": 6300 }
]
},
{
"Department": "Sales",
"Employees": [
{ "Id": 8, "Name": "Hannah", "Salary": 6400 }
]
}
]';
EXEC InsertHybridJSON @json;Employees from different departments are inserted while keeping their department assignments intact. By doing so, the original groupings from the JSON are preserved, and the relationship between each department and its employees remains clear and accurate.
Conclusion
Since SQL Server introduced the OPENJSON function, working with JSON data has become much more straightforward. Stored procedures make it possible to handle parsing and inserting JSON into tables in a way that reduces mistakes and keeps the process simple.
This guide showed several types of JSON that you are likely to encounter. It started with flat key-value pairs, moved on to nested objects, examined arrays inside JSON, and finished with hybrid structures that mix different patterns. These examples reflect the kinds of data you often get from APIs, log files, or other external sources.
By recognizing these patterns and knowing how to handle them, you can turn semi-structured JSON into well-organized SQL Server tables. This approach helps ensure that your data remains accurate and usable, no matter what source it comes from.
Next Steps
- Check out these related tips:
