Reading Data From JSON In SQL Server

In this article, I am going to explain, how to read data from a JSON string and process further.

Let's first understand what is JSON,

JSON stands for JavaScript Object Notation. It is a lightweight format for storing and transporting data. JSON is often used when data is sent from a server to a web page. JSON is "self-describing" and easy to understand

In JSON Data is in name/value pairs, and Data is separated by commas. Curly braces hold objects in JSON and Square brackets hold arrays.

Example

Below is very simple JSON representing Department data.

{
  "DepartmentId": "42127619",
  "DepartmentName": "Account"
}

Now let's move to OPENJSON. OPENJSON is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns. In other words, OPENJSON provides a rowset view over a JSON document. The JSON could be a single object or collection of JSON objects.

► When the JSON input has a single object, OPENJSON returns all the key/value pairs that it finds at the first level. 

This example explains the behavior of a single object : 

Declare @inputJson nvarchar(max) = '{
  "DepartmentId": "42127619",
  "DepartmentName": "Account"
}'
select * FROM OpenJson(@inputJson)

Output

► When JSON is having an array/collection of JSON objects, the OPENJSON returns all the elements of the array with their indexes.

The example below explains the same.

Declare @inputJson nvarchar(max) = '[{
  "DepartmentId": "42127619",
  "DepartmentName": "Account"
},
{
  "DepartmentId": "42127619",
  "DepartmentName": "Account"
}]'
select * FROM OpenJson(@inputJson)

Output

Let's try to understand the output columns a bit in detail.

The key in the screenshot above is the name of the property in the input object. If the JSON is having an array, it will be the index of the array element.

Value is the value of the property or the array element itself.

Type is an int value that contains the type of the value.

Here is an explanation of each int value returned in the type column.

Value of the Type column	JSON data type
0	null
1	string
2	number
3	true/false
4	array
5	object

I will add one more variation here in the examples of OPENJSON and will show you the output where the JSON has a complex structure. 

Declare @inputJson nvarchar(max) = '{
  "DepartmentId": "42127619",
  "DepartmentName": "Account",
  "Employees": [
    {
      "EmployeeId": "42127619_1",
      "EmployeeName": "Charan",
      "Salary": "10000"
    },
    {
      "EmployeeId": "42127619_2",
      "EmployeeName": "Chetan",
      "Salary": "11000"
    }
  ]
}'
select * FROM OpenJson(@inputJson)

In the example above, the Department JSON has an array of Employees along with it.

This is the output,

As you can see, the type of the Employees collection is 4 (array in the table above)

So far, the version of OPENJSON we saw was the default one.

There is another version of OPENJSON. wherein, we can specify a schema for the results using the WITH clause of the OPENJSON function.

When WITH option is used, we can define the only columns needed in the output. Along with columns, we can define the type of those columns and the path of the columns in the JSON. OPENJSON iterates through the array of JSON objects read the value on the specified path for each column and convert the value to the specified type.

Declare @inputJson nvarchar(max) = '{
"DepartmentId": "42127619", "DepartmentName": "Account", "Employees": [{
    "EmployeeId": "42127619_1",
    "EmployeeName": "Charan",
    "Salary": "10000"
}, {
    "EmployeeId": "42127619_2",
    "EmployeeName": "Chetan",
    "Salary": "11000"
}]
}
'
SELECT * FROM
OPENJSON(@inputJson)
WITH(Department bigint '$.DepartmentId', DepartmentName varchar(200)
'$.DepartmentName')

Output

I have selected only two columns here. Apart from that, the type of DepartmentId is specified as bigInt instead of a string that was coming with the default option of OPENJSON. Also, I renamed the DepartmentId to Department while selecting the records.

Here is another example below, wherein, we are going to read the Employees array which is part of the main JSON for the Department in form of JSON itself.

Declare @inputJson nvarchar(max) = '{
  "DepartmentId": "42127619",
  "DepartmentName": "Account",
  "Employees": [
    {
      "EmployeeId": "42127619_1",
      "EmployeeName": "Charan",
      "Salary": "10000"
    },
    {
      "EmployeeId": "42127619_2",
      "EmployeeName": "Chetan",
      "Salary": "11000"
    }
  ]
}'
SELECT * FROM  
 OPENJSON ( @inputJson )  
WITH(   
    DepartmentName     varchar(200)     '$.DepartmentName',  
    Employees nvarchar(max) '$.Employees' as JSON
) 

Output

AS JSON is used to specify that, the referenced property has an inner JSON object or an array/collection. when AS JSON option is specified, the type of the column has to be nvarchar(max).

If you would like to know more about JSON Path. you can refer to JSON Path Expressions.

The output of the select above can also be stored in a temporary table and can be used further to perform various operations.

Lastly, I will explain, how I came across this function and how I found it really helpful...

I was working on a project, where I was supposed to read data from an API response (which of course was in JSON format) and was supposed to store it in database tables and this, I was supposed to do by an automated task.. so I was going to set up a job, which will be executed on a scheduled time, daily and it will do all these things.

We designed a solution, wherein we used Logic apps to schedule our jobs. The jobs will call an Azure function which will have logic to call API and pass on the response to the database stored procedures (basically call the stored procedure).

So our stored procedure is going to perform operations in bulk. To read the JSON response, we used OPENJSON in the stored procedure.

OPENJSON was really handy for me, I had to read complex objects and it helped me a lot. After reading the JSON, I stored the data in a temporary table and then further used this temporary table data to perform other CRUD operations in the database.

That's about OPENJSON.

Do let me know if you found this article helpful. Also comment, if you would like to suggest any improvements.