JSON (JavaScript Object Notation) is a popular, light-weight, readable, text data format for structuring data. It is used mainly to transmit data between a server and web/mobile application.
JSON is an alternative to XML.
As per MSDN:
"JSON is also used for storing unstructured data in log files or NoSQL databases like Microsoft Azure DocumentDB. Many REST web services return results formatted as JSON text or accept data formatted as JSON. Most Azure services such as Azure Search, Azure Storage, and Azure DocumentDb have REST endpoints that return or consume JSON. JSON is also the main format for exchanging data between web pages and web servers using AJAX calls."
SQL Server 2016 has built-in JSON support. JSON is represented as NVARCHAR type. (not a native JSON type)
JSON in SQL Server 2016:
JSON Structure: Arrays(Stock), Objects(Order, Buy), Key-Value Pairs(Number, Date, Symbol, Price, Quantity)
Examples using built-in functions JSON_VALUE, JSON_QUERY, JSON_MODIFY:
Declare @varJSON nvarchar(max)
Set @varJSON =
{"Stock":[{"Order":{"Number":"12501109","Date":"2016-01-01T00:00:00"},"Symbol":"MSFT","Buy":{"Price":65.050,"Quantity":100}}]}
IF OBJECT_ID('Tempdb.dbo.#TMP', 'U') IS NOT NULL
Begin
Drop Table #TMP;
End
ISJSON:
Select ISJSON(@varJSON) as 'Is Valid JSON'
JSON_VALUE: Get Order Number
Select JSON_VALUE(@varJSON, '$.Stock[0].Order.Number') as 'Order Number'
JSON_VALUE: Get Order Number, with typo, returns NULL
Select JSON_VALUE(@varJSON, '$.Stock[0].Order.number')
JSON_VALUE: Returns a NULL because Order is an Object and JSON_VALUE only returns a value
Select JSON_VALUE(@varJSON, '$.Stock[0].Order')
JSON_QUERY: Get Order Object
Select JSON_QUERY(@varJSON, '$.Stock[0].Order') as 'Order Object'
JSON_QUERY: Get Stock Array
Select JSON_QUERY(@varJSON, '$.Stock') as 'Stock Array'
JSON Text Data Format into Tabular Format using OPENJSON:
Select * INTO #TMP From OPENJSON(@varJSON, N'$.Stock')
WITH (
Number varchar(10) N'$.Order.Number',
Date datetime N'$.Order.Date',
Customer varchar(150) N'$.Symbol',
Price decimal(18,3) N'$.Buy.Price',
Quantity int N'$.Buy.Quantity'
)
Select all from temp table:
Select * From #TMP
Tabular Format to JSON:
Select Number As [Order.Number], Date as [Order.Date], Customer as Symbol, Price as 'Buy.Price', Quantity as 'Buy.Quantity' from #TMP FOR JSON PATH, ROOT ('Stock')
Select @varJSON as 'Original'
JSON_MODIFY: Modify Order Number
Set @varJSON = (Select JSON_MODIFY(@varJSON, '$.Stock[0].Order.Number', '1500'))
Select @varJSON as 'Modified - Order Number'
JSON_MODIFY: Add DateShipped Key
Set @varJSON = (Select JSON_MODIFY(@varJSON, '$.Stock[0].Order.DateShipped', '2016-01-08'))
Select @varJSON as 'Modified - Addded DateShipped'
JSON_MODIFY: Remove DateShipped Key
Set @varJSON = (Select JSON_MODIFY(@varJSON, '$.Stock[0].Order.DateShipped', NULL))
Select @varJSON as 'Modified - Delete DateShipped'
JSON_MODIFY: Add Salesperson Object
Set @varJSON = (Select JSON_MODIFY(@varJSON, '$.Stock[0].Salesperson', 'Tim Stone'))
Select @varJSON as 'Modified - Append Salesperson Object'
OPENJSON:
Select * from OPENJSON(@varJSON) --where [key] = 'Stock'
Resources:
JSON Data (SQL Server)
Notes:
JSON functions are sase-sensitive
[ in JSON means an array
0-based Arrays
Use double quotes as escape character if have spaces
|
|
|
|
|
|