Karuta’s ASP & M$ SQLserver

Dicas, códigos e outras coisinhas de meus estudos para não esquecer…

JSON for SQL Server 2016

Posted by karuta em outubro 30, 2015

Problem

In previous tips, Introducing JSON for SQL Server 2016 and JSON Support in SQL Server 2016, you learned about the functionality available in CTP2: translating relational data into JSON string output, using FOR JSON AUTO and FOR JSON PATH. However, much more functionality is coming.

Solution

In CTP3, more elaborate functionality will be available, including the ability to translate JSON text into relational data, extract scalar values from JSON strings, and validate the format of JSON input. In this tip, I’ll demonstrate these three features briefly.

OPENJSON()

This rowset function will return relationally structured data from JSON input; the opposite of FOR JSON AUTO/PATH. A simple example:

DECLARE @json NVARCHAR(4000) = N'{
 "UserID" : 1,
 "UserName": "AaronBertrand",
 "Active": true,
 "SignupDate": "2015-10-01"
 }';

 SELECT * FROM OPENJSON(@json);

The output in this case is a rather generic list of key-value pairs, rather similar to the input:

key          value           type
----------   -------------   ----
UserID       1               2
UserName     AaronBertrand   1
Active       true            3
SignupDate   2015-10-01      1

However, you can also define more specific output structure using the WITH clause, for example (and adding a second row):

DECLARE @json NVARCHAR(4000) = N'[{
 "UserID" : 1,
 "UserName": "AaronBertrand",
 "Active": true,
 "SignupDate": "2015-10-01"
 },
 {
 "UserID" : 2,
 "UserName": "BobO''Neil",
 "Active": false,
 "SignupDate": "2014-12-13"
 }]';

 SELECT * FROM OPENJSON(@json)
 WITH 
 (
   UserID INT, 
   UserName NVARCHAR(64),
   Active BIT,
   [Started] DATETIME '$.SignupDate' -- remap column name
 );

The results are now a bit more relational:

UserID  UserName       Active  Started
------  -------------  ------  -----------------------
1       AaronBertrand  1       2015-10-01 00:00:00.000
2       BobO'Neil      0       2014-12-13 00:00:00.000

Another interesting use case for OPENSJSON() is to facilitate string splitting. In current versions of SQL Server, people typically resort to user-defined functions, CLR, or table-valued parameters . Watch how easy this work becomes with OPENJSON():

CREATE PROCEDURE dbo.ShowObjects
  @list NVARCHAR(4000)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT name 
    FROM sys.all_objects
    WHERE [object_id] IN 
    (
      SELECT CONVERT(INT, value)
        FROM OPENJSON(N'[' + @list + N']')
    );
END
GO

EXEC dbo.ShowObjects @list = N'-101,-102,-103';

Results:

all_columns
all_views
all_objects

You can see more elaborate examples of using OPENJSON() in these blog posts from Microsoft’s Jovan Popovic:OPENJSON – The easiest way to import JSON text into table and JSON Support in SQL Server 2016.

JSON_VALUE()

This function will extract specific scalar values from within the JSON text. As you can imagine, JSON strings can get pretty complex, so there is a set of syntax for determining exactly where in the hierarchy to extract from. A few simple examples:

DECLARE @json NVARCHAR(4000) = N'{
 "UserID" : 1,
 "Cars": [ 
   { "Year":2014, "Make":"Jeep",   "Model":"Grand Cherokee" },
   { "Year":2010, "Make":"Nissan", "Model":"Murano", "Options":
     [{ "AC":true,"Panoramic Roof":true }]
  ]
 }';

SELECT 
  UserID = JSON_VALUE(@json, '$.UserID'),
  Model1 = JSON_VALUE(@json, '$.Cars[0].Model'),
  Model2 = JSON_VALUE(@json, '$.Cars[1].Model'),
  Has_AC = JSON_VALUE(@json, '$.Cars[1].Options[0].AC');

Results:

UserID    Model1            Model2    Has_AC
------    --------------    ------    ------
1         Grand Cherokee    Murano    true

ISJSON()

This function returns a bit value: 1 if the input is a valid JSON document; 0 if not. On its own, the usage is quite simple:

DECLARE @json NVARCHAR(4000) = N'[{
 "UserID" : 1,
 "UserName": "AaronBertrand",
 "Active": true,
 "SignupDate": "2015-10-01"
 }]';

 SELECT ISJSON(@json),  -- returns 1
        ISJSON(N'foo'); -- returns 0

The value here is not so much to interrogate individual values, but rather to define things like validation logic in triggers and check constraints. For example, if I have a table where I am storing JSON text, I may want a constraint to prevent any garbage from getting in there (keep in mind that there is no JSON type; the existing NVARCHAR type is used).

CREATE TABLE dbo.JSONExample
(
  UserID INT PRIMARY KEY,
  Attributes NVARCHAR(4000),
  CONSTRAINT [No Garbage] CHECK (ISJSON(Attributes)) = 1
);

Now, if I try to insert garbage into this column:

INSERT dbo.JSONExample(UserID, Attributes) SELECT 1, N'foo';

I get the following error message:

Msg 547, Level 16, State 0
The INSERT statement conflicted with the CHECK constraint "No Garbage".
The conflict occurred in database "tempdb", table "dbo.JSONExample", 
column 'Attributes'.
The statement has been terminated.

However this will work fine:

INSERT dbo.JSONExample(UserID, Attributes) SELECT 1, N'{"garbage": false}';

Summary

The introduction of JSON functionality to SQL Server will allow for greater compatibility with existing applications and fewer barriers and cumbersome workarounds to sharing data between systems. In forthcoming related tips, I will talk in more detail about practical use cases for some of these features, and demonstrate the conversion of an EAV structure to one that uses JSON for storage instead.

 

By:

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

 
%d blogueiros gostam disto: