How to parse JSON data type data stored in SQL Server

Introduction

SQL Server allows storing JSON data in columns. In this article, we will show how to parse the data using the ZappySys JSON Parser Transform. Let's start with this demo.

Create the SQL Server column with JSON data

create table jsonTable
 (
 id int,
 jsoncol NVARCHAR(MAX)
 )

DECLARE @json NVARCHAR(MAX)
SET @json = 
N'[ 
 { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 }, 
 { "id" : 5,"info": { "name": "Jane", "surname": "Smith" }, "dob": "2005-11-04T12:00:00" } 
 ]'

insert into jsonTable values
 (1,@json)

Once that you have a column with JSON data, let's create an SSIS package.

1. In the control flow drag and drop the Data Flow task:
2. Double click the Data Flow Task and drag and drop the following components:
3. We will use the OleDB Data Source to connect to SQL Server to the column with JSON data.
4. In the JSON parser task transform task, write the JSON segment that you want to write: 
5. If you press the Preview button, you can see the JSON Data in a Grid ready to export to CSV, SQL Server or any other destination of your preference:
6. Finally, you can export the data into a file:
7. If everything is OK, you will be able to see the data exported from a SQL JSON column into CSV: 

Contact Us

If you have more question(s) feel free to contact us via Live chat or email to [e-mail]

https://zappysys.com/support/