SQL SERVER – JSON Support for Transmitting Data for SQL Server 2016 – Notes from the Field #114

SQL SERVER - JSON Support for Transmitting Data for SQL Server 2016 - Notes from the Field #114 Kathi [Note from Pinal]: In this episode of the Notes from the Field series database expert Kathi Kellenberger explains about JSON Support for Transmitting Data for SQL Server 2016. Kathi is an amazing instructor, she was the SQL author I have read in my early career. The reason, I love SQL Server because her writing has instigated love for this technology in me. Today she brings a relatively interesting topic for database experts. Read the experience of SQL SERVER – JSON from Kathi in her own words.


My favorite aspect of working with SQL Server is the T-SQL language. I love working with data, writing queries, and tuning queries when they don’t perform well. Whenever a new version of SQL Server is on the way, I can’t wait to hear what has been added to T-SQL.

I was really hoping to see additional T-SQL windowing functionality with 2016, but, so far, nothing has been announced. There is however, a very interesting programmability enhancement on the way: JSON support.

JSON, which stands for JavaScript Object Notation, is used for transmitting data and is used in many programming languages. JSON looks a bit like XML with square [] and curly {} brackets instead of angle <> brackets.

Here is an example comparing XML to JSON with data from the Production.Product table.

Solarwinds
XMLJSON
<Product>
  <Name>HL Road Frame - Black, 58</Name>
  <ListPrice>1431.5000</<wbr />ListPrice>
</Product>
<Product>
  <Name>HL Road Frame - Red, 58</Name>
  <ListPrice>1431.5000</<wbr />ListPrice>
</Product>
<Product>
  <Name>Sport-100 Helmet, Red</Name>
  <ListPrice>34.9900</ListPrice>
</Product>
<Product>
  <Name>Sport-100 Helmet, Black</Name>
  <ListPrice>34.9900</ListPrice>
</Product>
<Product>
  <Name>Mountain Bike Socks, M</Name>
  <ListPrice>9.5000</ListPrice>
</Product>
{</pre>
<pre>"Product": [
{
"Name": "HL Road Frame - Black, 58",
"ListPrice": 1431.5
},
{
"Name": "HL Road Frame - Red, 58",
"ListPrice": 1431.5
},
{
"Name": "Sport-100 Helmet, Red",
"ListPrice": 34.99
},
{
"Name": "Sport-100 Helmet, Black",
"ListPrice": 34.99
},
{
"Name": "Mountain Bike Socks, M",
"ListPrice": 9.5
}
]
}

The additions to T-SQL to support JSON are listed below. There is one new clause and

NameTypePurpose
FOR JSONClauseReturn JSON from a SELECT statement
OPENJSONFunctionReturn JSON data in tabular format
ISJSONFunctionReturns TRUE if a string is a valid JSON stringJSON
JSON_VALUEFunctionReturns a single value from aJSON string
JSON_QUERYFunctionReturns an array of values from a JSON string

four functions:

I used the FOR JSON clause to generate the example JSON data above. Here is the code I used:

SELECT TOP(5) Name, ListPrice
FROM Production.Product
WHERE ListPrice &gt; 0 AND Name NOT LIKE '%Seat%'
FOR JSON PATH, ROOT('Product');

There are several options, so be sure to take a look at the documentation for more information. Here is an example script demonstrating the functions:

--Save a string in JSON format to a variable
DECLARE @Books NVARCHAR(MAX) = N'
{"Books":
{"BookList":
[	{"Book":
{	"Title":"Microsoft SQL Server T-SQL Fundamentals",
"Authors":"Itzik Ben Gan"
}
},	
{"Book":
{
"Title":"The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling",
"Authors":"Ralph Kimball; Margy Ross"
}
},
{"Book":
{
"Title":"SQL Server Concurrency: Locking, Blocking, and Row Versioning",
"Authors":"Kalen Delaney"
}
}	
]
}	
}';
IF ISJSON(@Books) = 0 BEGIN 
   PRINT 'Invalid JSON string';
END 
ELSE BEGIN
   --Turn the JSON string into tabular format
   SELECT * 
   FROM OPENJSON(@Books,'$.Books.BookList')
   WITH (
       Title NVARCHAR(100) '$.Book.Title',
       Authors NVARCHAR(100) '$.Book.Authors');
   --Return the BookList array in JSON format
   SELECT JSON_QUERY(@Books,'$.Books.BookList') AS [Book Array];
   --Return the second row as JSON
   SELECT JSON_QUERY(@Books,'$.Books.BookList[1]') AS [Second Book];
   --Return the title from the second row
   SELECT JSON_VALUE(@Books,'$.Books.BookList[1].Book.Title') AS [Book Title];
END;

Here are the results:
SQL SERVER - JSON Support for Transmitting Data for SQL Server 2016 - Notes from the Field #114 114
The most challenging aspect for me when writing the previous example, was creating a valid JSON string. The path attribute, that string starting with $ used with the functions, looks a bit intimidating, but it is actually simple. The $ is the starting point. Follow the path in the data to get what you want.

Let’s take a look at the last query:

SELECT JSON_VALUE(@Books,'$.Books.<wbr />BookList[1].Book.Title') AS [Book Title];

The JSON_VALUE function takes two parameters, the JSON string and a path. In this case, I am specifying the item from the array to extract. It is found in Books and the array is called BookList. I want the item at index 1. The item is called Book and I am retrieving the Title property.

The new functionality will make it easier for developers to work with SQL Server data regardless of the programming language they use.

Kathi Kellenberger is the author of Beginning T-SQL, Expert T-SQL Window Functions in SQL Server, and PluralSight course T-SQL Window Functions.

SQL SERVER - JSON Support for Transmitting Data for SQL Server 2016 - Notes from the Field #114 notes-82-3 If you would like to learn more about T-SQL window functions, be sure to check out my latest book Expert T-SQL Window Functions in SQL Server or my Pluralsight course T-SQL Window Functions.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER 2016: Creating Simple Temporal Table
Next Post
SQL SERVER – Huge Transaction Log (LDF) for Database in Availability Group

Related Posts

1 Comment. Leave new

  • Michael Sultana
    May 18, 2017 2:50 pm

    Hi,
    Is there any way where you can specify the path as variable in open json like the below

    DECLARE @JSON NVARCHAR(MAX)

    set @JSON = ‘{ “amounts”: [
    {
    “Charge_Id”: “368”,
    “Amount”: “800”,
    “FineType_Id”: 3,
    “user_id”: 2
    },
    {
    “Charge_Id”: “368”,
    “Amount”: “600”,
    “FineType_Id”: 4,
    “user_id”: 2
    }
    ]

    }’;

    DECLARE @counter int
    DECLARE @expression NVARCHAR(50)
    set @counter = 0
    set @expression = ‘$.amounts[‘+CONVERT(NVARCHAR(10),@counter)+’]’

    SELECT *
    FROM OPENJSON(@JSON,@expression)
    WITH(Charge_Id int, Amount money, FineType_Id int, user_id int)AS JSON

    Since this causes an error

    Msg 102, Level 15, State 1, Line 31
    Incorrect syntax near ‘@expression’.
    Msg 319, Level 15, State 1, Line 32
    Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

    Thanks for your help

    Reply

Leave a Reply

Menu