Parse JSON using SQL Server 2014 or lower
Hello, this time we will show how to parse JSON in SQL Server 2014 or other versions. we will use a simple tool easy to use and we do not need programming knowledge.
Solution
In this article we will learn how to parse JSON in SQL Server using a Free tool that can be installed with SSIS that can be downloaded from the internet. If you do not have any experience in SSIS, do not worry. This is a great opportunity to learn. This article will not take long.
Requirement
- First, make sure that you have SQL Server.
- Also, check to install SSIS (it is included with the SQL Server installer).
- And finally, make sure to install SSDT
Getting Started
SQL Server 2016 and later versions include JSON support. You can work with JSON in T-SQL like you did with XML. However, it requires some programming knowledge to work with it and it is not simple if you do not have JSON or XML experience.
In this example, we will show 2 examples.
- First, we will show a simple example of how to parse a JSON file and get values in variables.
- Secondly, we will show how to get JSON values from a variable and store the variable values in SQL Server.
1. Example to get values from JSON into variables
Let’s start the first demo
- First of all, we will first download a free tool named SSIS JSON Parser Task. This task will help us to parse the JSON data. Once installed, drag and drop to the design pane. With the installer, you will also have another free tool. The ZS Logging Task
Drag and drop json parser task
- Secondly, we will create two SSIS variables first that will store the JSON values. In SSDT, go to the SSIS menu and select Variables. Create two variables. CustomerID and CustomerName of type String:
ssis variable
- Also, we will use the following JSON text:
1234567{"CustomerID": "ALFKI","CustomerName": "Alfredo Koli","Age": 55,"LocationInfo": {"City":"Newyork", "State":"NY", "Zip":"23234"},"OrderIDList": [111,222,333,444,555]} - Using the ZS JSON parser task, map CustomerID and CustomerName to the user variables created before:
Mapping JSON values into variables
- For demonstration purposes, the age will be set to LOG-TO-OUTPUT. This means that the values will be displayed in the SSIS Output pane:
Send value to log in SSIS
- In addition, we will verify the values of the variables. To do that, we will use another free tool. The ZS Logging Task that comes with the ZS JSON Parser task. This tool can display the variable values in the SSIS output (ExecutionLog) in a MessageBox a file or a variable. In this example, we will show the variable values in the SSIS output:
Set values to ExecutionLog
- Finally, run the packages and check the output. If you cannot see the output, go to View in the menu and select Output.
Check SSIS variables in log
Also, the JSON Parser task shows that the age is 55. You can verify this value in the JSON data in step 3. Also, the Logging task is showing the CustomerId and the CustomerName values that can be verified and compared with the step 3 that contains the JSON objects and arrays.
2. Second example. Storing and showing how to parse JSON in SQL Server
In the first example, we learned how to parse JSON values and store them in SSIS variables and how to send values to the output. In this new example, we will show how to parse JSON values in SQL Server.
- First of all, will be to create a simple table in SQL Server to store JSON values:
12345678910111213CREATE TABLE [dbo].[book]([author] [nchar](50) NULL,[isbn] [nchar](20) NULL,[price] [decimal](18, 0) NULL,[category] [nchar](20) NULL,[id] [smallint] IDENTITY(1,1) NOT NULL,CONSTRAINT [PK_book] PRIMARY KEY CLUSTERED([id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO - Secondly, we will use the following JSON data.
12345678910111213141516171819202122232425262728293031{ "store": {"book": [{ "category": "reference","author": "Nigel Rees","title": "Sayings of the Century","price": 8.95},{ "category": "fiction","author": "Evelyn Waugh","title": "Sword of Honour","price": 12.99},{ "category": "fiction","author": "Herman Melville","title": "Moby Dick","isbn": "0-553-21311-3","price": 8.99},{ "category": "fiction","author": "J. R. R. Tolkien","title": "The Lord of the Rings","isbn": "0-395-19395-8","price": 22.99}],"bicycle": {"color": "red","price": 19.95}}} - Also, will create the variables first. The inputjson will contain the JSON data from the previous step:
ssis variables
- Once it is done, will be to insert a variable this time. We will use the Insert Variable option and insert the inputjson variable that contains the JSON values. We will also use a filter to show just the 3rd book information (the first element index is 0). Path filters in JSON are out of the scope of this article, but you can read a nice article about the topic here. Press the Parse Field to parse the values. Finally, map the author, category, isbn and price variables:
Using filters
- After that, drag and drop the Execute SQL Task and join it with the JSON Parser to receive the variable values. Specify a connection with the server name and database name where the table of step 1 was created. In SQLStatement, write the following query:
This query will insert the 4 SSIS parameters in SQL Server:12345678910INSERT INTO [dbo].[book]([author],[isbn],[price],[category])VALUES(?,?,?,?)
Execute SQL Task
- Also, in parameter mapping, specify the variable name, direction, data type and Paremeter name:
Map variables into SQL
- Finally, run the package and you will be able to see the value stored in SQL Server book table:
Check results
Conclusion
As a conclusion, we can say that it is possible to parse JSON in SQL Server using SSIS additional tasks and it does not require to know T-SQL or code to get JSON values.
References
Finally, you can use the following links for more information about REST API and SQL Server:
keywords: parse JSON in SQL Server
