JSON (JavaScript Object Notation) is an open standard file format, it is a text based data exchange medium, now a days most of the API gateway's returns JSON output, and other applications consume it, if we talk about SQL and Database area many places we get requirement to read JSON data, load that in a database to process further for OLAP application's.
Microsoft SQL server 2016 and above version's support JSON transactions with multiple helpful functions, which helps us to make our life easy. in this article i am trying to simplify how to work with JSON and MS SQL server.
Before that just look into this diagram to understand importance and uses of JSON.
lets begin with Sample JSON Code, you can find more details of JSON from https://www.json.org
Here is a sample JSON information data, with multiple attributes.
[
{"name":"anup",
"company":"CYZ",
"city":"pune",
"skills":["SQL","MSBI","C#","power
BI"]
},
{"name":"vishal",
"company":"IXZ",
"city":"hyderabad",
"skills":["SQL","MSBI","reports"]
},
{"name":"moin",
"company":"DXZ",
"city":"hyderabad",
"skills":["SQL","MSBI","C#","informatica"]
}
]
now the first question comes in our mind is how to read this in SQL server and display it in tabular format? for this we have a Function called OPENJSON() similar like OPENROWSET, OPENXML in SQL server, lets see a simple way to read it.
1) OPENJSON()
declare @json nvarchar(1000);
-- Sample JSON string
set @json = '
[
{"name":"anup",
"company":"CYZ",
"city":"pune",
"skills":["SQL","MSBI","C#","power
BI"]
},
{"name":"vishal",
"company":"IXZ",
"city":"hyderabad",
"skills":["SQL","MSBI","reports"]
},
{"name":"moin",
"company":"DXZ",
"city":"hyderabad",
"skills":["SQL","MSBI","C#","informatica"]
}
]'
-- selecting JSON Text in tabular format
select * from OPENJSON(@json)
but output of above query is not exact tabular format so we need to read this column wise.(Column names mentioned in with clause should match with json attributes)
select * from OPENJSON(@json)
with(
name varchar(10),
company varchar(20),
city varchar(10),
skills varchar(10) '$.skills[1]',
skills varchar(10) '$.skills[2]',
skills varchar(10) '$.skills[3]',
skills varchar(10) '$.skills[4]'
)
-----------------------------------------------------------------------------------------------------------------------
2) Read JSON_VALUE consider we have inserted JSON as a text in column and we have to extract values out this then we have JSON_QUERY, JSON_VALUE Functions in MS SQL server.
--Inserting JSON Text as column value in
table
Create table #jsontbl (id int identity(1,1), jsoncol varchar(3000))
insert into #jsontbl(jsoncol) Values (@json)
select * from #jsontbl
see the query to read values out of json text and to validate json text
-- Example 1
select
JSON_VALUE(jsoncol,'$[1].name') as 'Name',
JSON_VALUE(jsoncol,'$[1].city') as 'City',
JSON_QUERY(jsoncol,'$[1].skills') as Skills
from #jsontbl
where ISJSON(jsoncol)>0
Updating JSON
-- Updating JSON Value
DECLARE @json1 NVARCHAR(MAX);
SET @json1 =
'{
"info":
{ "address": [
{"town":
"Pune"},
{"town": "Hyderabad"},
{"town": "Delhi"}
]
}
}';
SET @json1 = JSON_MODIFY(@json1, '$.info.address[2].town', 'Mumbai');
Print @json1;
-----------------------------------------------------------------------------------------------------------------------
3) Exporting SQL Data in JSON Format. this is again a very important if we want to give data feed to other application in a json format. use - JSON PATH
select [id], [event],[operator],[executionid]
from MyDB..sysssislog
for JSON PATH
-----------------------------------------------------------------------------------------------------------------------
4) Open JSON text file from physical location to SQL server and process further. we can write query something like this.
-- Read JSON Data from txt file
Select * from OPENROWSET(BULK 'C:\Data\jsondata.txt',SINGLE_BLOB) tbl
-----------------------------------------------------------------------------------------------------------------------
i tried to bring here all JSON topics and functions from SQL server 2016 and later versions with more simpler manner so one can expand this code further as per your requirement, Happy coding !!!
Thank you !
