Advertisements

Archive

Posts Tagged ‘FOR JSON PATH’

Export/Convert Table or SQL Query data to JSON string format | SQL Server 2016 – Part 1

June 1, 2015 6 comments

In my [previous post] we saw JOSN added as a new feature in SQL Server 2016.
 

–> Just like XML now you can also:

1. Store JSON data in SQL Server in table columns as NVARCHAR datatype.

2. Export SQL tables rows to JSON data.

3. Query external JSON data and store back in SQL tables.
 

–> Note:

– With this CTP2 release you can only export data as JSON string.

– But with the release of CTP3 you will also be able to read JSON data by T-SQL query and convert it into tabular (row/column) format, and will support indexes.
 

–> Just like XML for exporting JSON data you can use FOR JSON [AUTO | PATH] syntax:

1. FOR JSON AUTO: option automatically creates a nested JSON data with sub arrays based on the table hierarchy used in the Query. The AUTO option must have a FROM clause.

2. FOR JSON PATH: option enables you to define the structure of output of JSON data using the column names with aliases by using a dot separator.
 

–> Let’s see how export to JSON works:

– I’ll create a sample table and insert few rows in it:

CREATE TABLE Students (
	ID INT IDENTITY(1,1) NOT NULL, 
	FirstName VARCHAR(255), 
	LastName VARCHAR(255), 
	Class INT,
	Marks DECIMAL(3,1)
)

INSERT INTO Students (FirstName, LastName, Class, Marks)
SELECT 'Manoj',   'Pandey', 10, 80.5
UNION ALL
SELECT 'Saurabh', 'Sharma', 11, 82.7
UNION ALL
SELECT 'Kanchan', 'Pandey', 10, 90.5

 

1. Let’s check the “FOR JSON AUTO” option:

SELECT ID, FirstName, LastName, Class, Marks 
FROM Students
FOR JSON AUTO -- here

– Output with AUTO option:
SQL Server 2016 JSON 01

– Output with AUTO and ROOT() option:

SELECT ID, FirstName, LastName, Class, Marks 
FROM Students
FOR JSON AUTO, ROOT('StudList') -- here

– This is how a formatted JSON looks like:

{
  "StudList": [
    {
      "ID": 1,
      "FirstName": "Manoj",
      "LastName": "Pandey",
      "Class": 10,
      "Marks": 80.5
    },
    {
      "ID": 2,
      "FirstName": "Gaurav",
      "LastName": "Pandey",
      "Class": 11,
      "Marks": 82.7
    },
    {
      "ID": 3,
      "FirstName": "Garvit",
      "LastName": "Pandey",
      "Class": 10,
      "Marks": 90.5
    }
  ]
}

This option as mentioned previously formats the JSON document automatically based upon the columns provided in the Query.
 

2. Now let’s check the “FOR JSON PATH” option: with this option you can use the dot syntax as used in below Query to form a nested output.

SELECT 
	ID,
	FirstName AS "StudentName.FirstName", 
	LastName AS "StudentName.LastName", 
	Marks
FROM Students
FOR JSON PATH -- here

– Output with PATH option:

[  
   {  
      "ID":1,
      "StudentName":{  
         "FirstName":"Manoj",
         "LastName":"Pandey"
      },
      "Marks":80.5
   },
   {  
      "ID":2,
      "StudentName":{  
         "FirstName":"Saurabh",
         "LastName":"Sharma"
      },
      "Marks":82.7
   },
   {  
      "ID":3,
      "StudentName":{  
         "FirstName":"Kanchan",
         "LastName":"Pandey"
      },
      "Marks":90.5
   }
]

As you can see with PATH option you can create wrapper objects (here “StudentName”) and nest properties (here “FirstName” & “LastName”).
 

–> You can also check this demo in this video:


 

With the current release of CTP2 here you saw how we can export a SQL Table rows to JSON data. As soon as CTP3 will release we will see how can we read data back from from JSON string and convert it to tabular (row/column) format.
 

–> Check my [next post] on how to Import/Read JSON string and convert it in rational-tabular format in form of rows/columns.
 

Check more about JSON support on [MSDN BoL].
 


Advertisements

Native JSON support – new feature in SQL Server 2016

May 27, 2015 2 comments

In my [previous blog post] on “SQL Server 2016 Enhancements” we discussed about the new features coming in. Native JSON support is on of those feature that will help you support and manage NoSQL/Unstructured document data within a SQL Server database.
 

–> JSON (or JavaScript Object Notation) is a popular, language-independent, lightweight data-interchange format used in modern web and mobile applications, as well for storing Unstructured data. JSON is an alternate to XML and is more compact than that format, and thus has become the first choice for many applications that need to move data around on the web. One of the biggest reasons JSON is becoming more important than XML is that XML has to be parsed with an XML parser, while JSON can be parsed by a standard JavaScript function. This makes it easier and faster than working with XML.

For more details on JSON check at json.org

–> JSON in SQL Server 2016 is not treated as a separate Datatype by the DB Engine, like XML. For example, appending FOR JSON AUTO to a standard SELECT statement returns the result set in a JSON format. The JSON data is stored as NVARCHAR type, unlike XML datatype for XML data, thus JSON will be supported wherever NVARCHAR is supported.

–> Here is a sample & simple query to convert a row-set into a JSON format:

SELECT TOP 10 
	M.ProductModelID, 
	M.Name AS [ProductModel.Name],
	ProductID, 
	P.Name AS [Product.Name], 
	ProductNumber, 
	MakeFlag
FROM Production.Product P
INNER JOIN Production.ProductModel M 
ON P.ProductModelID = M.ProductModelID 
FOR JSON PATH, ROOT('ProductModel') -- here, JSON syntax similar to XML

Check the last line, the syntax is almost similar to XML datatype. So, if you familiar with XML syntax in T-SQL, working with JSON will be a seamless experience.
 

Check my [next post] on how to work with JSON string with SQL queries.
 

Check this video on how to work with JSON and SQL:


 

–> More from MS BoL and whitepapers on JSON support:

JSON syntax is simple and human-readable. JSON values consist of name/value pairs, and individual values are separated by commas. Objects are containers of one or more name/value pairs and are contained within curly brackets. JSON arrays can contain multiple objects, and arrays are contained within square brackets.

JSON is the storage format used in several NoSQL engines, including Azure DocumentDB. DocumentDB uses Azure Blob storage to store schema-less documents, but provides a rich SQL query dialect that allows you to conduct SQL queries over the data contained in the documents. In addition to DocumentDB, Azure Search also utilizes JSON. Azure Search is a fully managed search solution that allows developers to embed sophisticated search experiences into web and mobile applications without having to worry about the complexities of full-text search and without having to deploy, maintain, or manage any infrastructure.

The combination of SQL Server’s new support for JSON with these other Microsoft tools enables many scenarios for moving data back and forth between relational and schema-less storage and the applications that access such data. For example, these tools would allow you to set up periodical extractions of relational data in SQL Server, transforming the data into JSON and loading it to a JSON-based Azure DocumentDB storage that is searchable from a mobile device, along with data from many other sources, utilizing Azure Search.
 

Check more on [MSDN BoL] for JSON support in SQL Server 2016.