SQL Server 2016 and Azure SQL database enables us to parse JSON text and transform into a tabular format.
What is JSON?
JSON stands for JavaScript Object NotationJSON is mainly used to store and exchange information
JSON data is well organized, easy-to-access and in human-readable format of data that can be accessed in a logical manner
JSON is self-describing and easy to understand
JSON is a lightweight data-interchange format
JSON is language independent
JSON is much more compact than XML and is becoming the first choice for many application that needs to move data around the web
JSON is also the storage format used in several DB, NoSQL engines such as CouchDB, MongoDB, OrientDB, PostgreSQL including Microsoft Azure DocumentDB.
Why have JSON in SQL Server?
Usage of JSON has increased exponentially in Web API's and it has become lingua franca of Web applications besides it also supports direct querying to Hadoop. This is the reason why Microsoft has come up with built-in support for storing, managing and parsing JSON data in SQL Server 2016 CTP 2.2 or later.Key JSON capabilities in SQL Server
We use NVARCHAR datatype to store JSON data or documents in SQL Server 2016.SQL Server provides built-in functions and operators for performing following operations on JSON text:
- Parse JSON text and read or modify values.
- Transform arrays of JSON objects into a table format.
- Run any Transact-SQL query on the converted JSON objects.
- Format the results of Transact-SQL queries in JSON format.
Parse JSON text and read or modify values
For extracting values from JSON objects, the following built-in functions are available:
JSON_VALUE( jsonText, path ) parses jsonText and extracts scalar values on the specified JavaScript-like path.
JSON_QUERY( jsonText, path ) parses jsonText and extracts objects or arrays on the specified JavaScript-like path.
SET @json =
N'{
"info":{
"type":1,
"address":{
"town":"Lucknow",
"state":"Uttar Pradesh",
"country":"India"
},
"tags":["Lucknow Eye", "Cricket Stadium"]
},
"type":"Basic"
}'
SELECT
JSON_VALUE(@json, '$.type') as type,
JSON_VALUE(@json, '$.info.address.town') as town,
JSON_QUERY(@json, '$.info.tags') as tags
Transform arrays of JSON objects into a table format
OPENJSON function enables us to reference some array in JSON text and returns elements from that array:
SELECT value FROM OPENJSON(@json, '$.info.tags');
Converting JSON Data to Rows and Columns with OPENJSON
DECLARE @json NVARCHAR(4000) SET @json = '{"name":"Ashutosh","surname":"Sharma","age":28,"skills":["SQL","C#","Performance Tuning"]}';
DECLARE @json NVARCHAR(4000) SET @json = '{"name":"Ashutosh","surname":"Sharma","age":28,"skills":["SQL","C#","Performance Tuning"]}';
SELECT * FROM OPENJSON(@json) WITH (name nvarchar(30), surname nvarchar(30), age int, skills nvarchar(max) as json)
Run any T-SQL query on converted JSON Objects
SELECT Id, FirstName, LastName,
JSON_VALUE(InfoJSON, '$.info.”social security number”') as SSN, JSON_QUERY(InfoJSON, '$.skills') as Skills
FROM Person AS t
WHERE ISJSON( InfoJSON ) > 0
AND JSON_VALUE(InfoJSON, '$.Type') = 'Student'
JSON_VALUE(InfoJSON, '$.info.”social security number”') as SSN, JSON_QUERY(InfoJSON, '$.skills') as Skills
FROM Person AS t
WHERE ISJSON( InfoJSON ) > 0
AND JSON_VALUE(InfoJSON, '$.Type') = 'Student'
ISJSON function tests whether a string contains valid JSON
Format result of any T-SQL query in JSON format
SELECT H.SalesOrderID, H.Status, H.PurchaseOrderNumber, H.ShipDate, P.ProductID
FROM [Sales].[SalesOrderHeader] H
INNER JOIN [Sales].SalesOrderDetail D ON H.SalesOrderID = D.SalesOrderID
INNER JOIN [Production].[Product] P ON D.ProductID = P.ProductID
WHERE H.SalesOrderID IN (43663, 43687)
FOR JSON AUTO
GO
AUTO mode with the FOR JSON clause to format the JSON output automatically based on the structure of the SELECT statement.
PATH mode with the FOR JSON clause to maintain full control over the format of the JSON output. You can create wrapper objects and nest complex properties.
SQL Server 2016 introduces built-in support for storing, managing and parsing JSON data. In this article of the series, I discussed using OPENJSON to read and parse JSON data, convert it to tabular format other newly introduced functions to work with JSON data. Also discussed how we can convert tabular data to JSON format using the FOR JSON clause.
In the next article, I will talk about storing JSON data in SQL server and various options available. Although JSON is stored in a text column, it is not just “a plain text." SQL Server has built-in support for optimizing storage of text columns using various compression mechanisms such as UNICODE compression that can provide up to a 50% compression ratio. You can also store JSON text in column store tables or compress it explicitly using the built-in COMPRESS function that uses the GZip algorithm. We will discuss all that in details.
SQL Server 2016 introduces built-in support for storing, managing and parsing JSON data. In this article of the series, I discussed using OPENJSON to read and parse JSON data, convert it to tabular format other newly introduced functions to work with JSON data. Also discussed how we can convert tabular data to JSON format using the FOR JSON clause.
In the next article, I will talk about storing JSON data in SQL server and various options available. Although JSON is stored in a text column, it is not just “a plain text." SQL Server has built-in support for optimizing storage of text columns using various compression mechanisms such as UNICODE compression that can provide up to a 50% compression ratio. You can also store JSON text in column store tables or compress it explicitly using the built-in COMPRESS function that uses the GZip algorithm. We will discuss all that in details.
Comments
Post a Comment