SQL Server 2016 JSON支持

hyadijxp

　　SQL Server 2016 JSON支持
　　英文原文：
　　https://www.simple-talk.com/sql/learn-sql-server/json-support-in-sql-server-2016/
　　最近，SQL Server 跟上其他关系型数据库的步伐，提供了有用的JSON支持的功能。这是一个好的开始，尽管它还不像已存在的XML支持那样完善。对于许多应用，能够满足需求。
　　SQL Server 2016最后添加了JSON支持，它是一个在不同源类型之间交换数据的轻量格式，类似于XML。JSON是JavaScript对象表示（JavaScript Object Notation）的简称，基于JavaScript编程语言的子集，备注为易读、易于被计算机解析和生成。
　　根据微软的说明，它是微软connect网站排名最高的需求之一，他被包含在SQL Server是受欢迎的消息。也就是说，除非你期待和XML同样健壮的支持。SQL Server 2016非常完善的支持JSON，也没有像在PostgreSQL找到的功能。
　　SQL Server 2016没有引入JSON专属数据类型，因此没有像XML数据类型大量可用方法。SQL Server 2016继续使用NVARCHAR类型存储JSON数据。然而，提供了一些重要的T-SQL语言元素，用于比以前更易于处理JSON，所以微软至少在正确的方向前进，即使仍然要跟上步伐。
　　开始了解JSON
　　
　　尽管JSON比我们涵盖的内容更加复杂，在开始关于SQL Server支持之前，它有助于基本的理解JSON代码片段的组成。最基本的，JSON片段可以包含对象、数组，或都包含。一个对象是一个为排序的，一个或多个名称/值对（属性）的集合，用大括号括起来，像如下示例一样显示：
{"FirstName":"Terri", "Current":true, "Age":42, "Phone":null}　　对于每个属性，名称组件（FirstName，Current，Age，Phone）用双引号引起来，后面跟着逗号。名称组件，有时被作为键，总是一个字符串。属性的值有稍微不同的规则。如果值是字符串，你应该用双引号引用。如果是一个数字值、布尔值（true或false），或null值，不使用引号。
　　一个数组只是一个排序的值的集合，用中括号括起来，示例如下：
["Terri", true, 42, null]　　数组支持和对象一样的值类型：字符串、数字、true、false或null。对象和数组可以包含其他对象和数组作为他们的值，像下面的示例一样，提供了嵌套结构的方式：
{　　
   "Employees":[
　　
      {
　　
         "Name":{
　　
            "First":"Terri",
　　
            "Middle":"Lee",
　　
            "Last":"Duffy"
　　
         },
　　
         "PII":{
　　
            "DOB":"1971-08-01",
　　
            "NatID":"245797967"
　　
         },
　　
         "LoginID":"adventure-works\\terri0"
　　
      },
　　
      {
　　
         "Name":{
　　
            "First":"Roberto",
　　
            "Middle":null,
　　
            "Last":"Tamburello"
　　
         },
　　
         "PII":{
　　
            "DOB":"1974-11-12",
　　
            "NatID":"509647174"
　　
         },
　　
         "LoginID":"adventure-works\\roberto0"
　　
      }
　　
   ]
　　
}
　　在顶层，有一个包含单一属性的JSON对象。属性的名称是Employees，值是一个数组，它包含两个值。每个数组值是包含了Name，PII和LoginID属性的一个JSON对象。Name和PII值也是JSON对象，包含他们自己的名称/值对。
　　在完成本文中的示例后，你会更好的理解这些大量的组件是如何工作的。
　　格式化查询结果为JSON
　　
　　在SQL Server 2016中支持的一个JSON相关的属性是，可以通过添加FOR JSON从句到SELECT语句，来以JSON格式返回数据。我们来浏览下如何使用FOR JSON从句以JSON格式返回数据的基础，使用AUTO参数或PATH参数。
　　首先，我们需要构造一些工作所需数据。下面的SELECT语句从AdventureWorks2016CTP3数据库的vEmployee视图返回两行：
USE AdventureWorks2016CTP3;　　
go
　　
SELECT FirstName, MiddleName, LastName,
　　
EmailAddress, PhoneNumber
　　
FROM HumanResources.vEmployee
　　
WHERE BusinessEntityID in (2, 3);
　　它返回了以下结果，尽管你可能会看到与最后产品不同的结果，因为数据和示例基于SQL Server 2016 CTP3版本：
　　FirstName
　　MiddleName
　　LastName
　　EmailAddress
　　PhoneNumber
　　Terri
　　Lee
　　Duffy
　　terri0@adventure-works.com
　　819-555-0175
　　Roberto
　　NULL
　　Tamburello
　　roberto0@adventure-works.com
　　212-555-0187
　　AUTO模式
　　
　　以JSON格式返回结果，为了支持一个特定应用，我们只需添加FOR JSON从句到该语句，如下示例：
SELECT FirstName, MiddleName, LastName,　　
EmailAddress, PhoneNumber
　　
FROM HumanResources.vEmployee
　　
WHERE BusinessEntityID in (2, 3)
　　
FOR JSON AUTO;
　　注意到从句包含AUTO参数，表明结果应该以AUTO模式返回。当你指定该模式，数据库引擎自动决定JSON格式，基于SELECT列表和FROM从句中表中的列的字段顺序。在这里，FOR JSON AUTO从句导致SELECT语句返回如下结果。
[{"FirstName":"Terri","MiddleName":"Lee","LastName":"Duffy","EmailAddress":"terri0@adventure-works.com","PhoneNumber":"819-555-0175"},{"FirstName":"Roberto","LastName":"Tamburello","EmailAddress":"roberto0@adventure-works.com","PhoneNumber":"212-555-0187"}]　　从这些结果，你可以看到JSON的输出包含含有两个值的数组，每个值是一个JSON对象。毫不奇怪，因为结果变得更加复杂，更加难懂。在这种情况下，你可以使用一个本地的或在线的JSON格式化/验证工具将JSON片段转换为更加易读。例如，我将之前的结果传给格式化工具https://jsonformatter.curiousconcept.com/并获得如下JSON：
[　　
   {
　　
      "FirstName":"Terri",
　　
      "MiddleName":"Lee",
　　
      "LastName":"Duffy",
　　
      "EmailAddress":"terri0@adventure-works.com",
　　
      "PhoneNumber":"819-555-0175"
　　
   },
　　
   {
　　
      "FirstName":"Roberto",
　　
      "LastName":"Tamburello",
　　
      "EmailAddress":"roberto0@adventure-works.com",
　　
      "PhoneNumber":"212-555-0187"
　　
   }
　　
]
　　如你所见，现在更容易看到顶层数组和包含的两个对象值。每个对象属于SELECT语句返回的一行。向前推进，我们将只显示更加可读的格式化结果，但是SQL Server以单行值返回数据，没有所有的空格和空行，就像上面看到的。
　　现在你尝试下FOR JSON AUTO从句，让我们看看当连接表的时候发生了什么：
SELECT e.BirthDate, e.NationalIDNumber, e.LoginID,　　
p.FirstName, p.MiddleName, p.LastName
　　
FROM HumanResources.Employee e INNER JOIN Person.Person p
　　
ON e.BusinessEntityID = p.BusinessEntityID
　　
WHERE e.BusinessEntityID in (2, 3)
　　
FOR JSON AUTO;
　　因为我们的SELECT语句变得更加复杂，输出为JSON格式，显示为如下结果：
[　　
   {
　　
      "BirthDate":"1971-08-01",
　　
      "NationalIDNumber":"245797967",
　　
      "LoginID":"adventure-works\\terri0",
　　
      "p":[
　　
         {
　　
            "FirstName":"Terri",
　　
            "MiddleName":"Lee",
　　
            "LastName":"Duffy"
　　
         }
　　
      ]
　　
   },
　　
   {
　　
      "BirthDate":"1974-11-12",
　　
      "NationalIDNumber":"509647174",
　　
      "LoginID":"adventure-works\\roberto0",
　　
      "p":[
　　
         {
　　
            "FirstName":"Roberto",
　　
            "LastName":"Tamburello"
　　
         }
　　
      ]
　　
   }
　　
]
　　来自Person表的信息是，现在它是p数组的一部分，它是父对象的一个值。我们重新调用，AUTO模式基于SELECT列表和FROM从句表中的字段顺序格式化结果，所以让我们混合列顺序：
SELECT p.FirstName, p.MiddleName, p.LastName,　　
e.BirthDate, e.NationalIDNumber, e.LoginID
　　
FROM HumanResources.Employee e INNER JOIN Person.Person p
　　
ON e.BusinessEntityID = p.BusinessEntityID
　　
WHERE e.BusinessEntityID in (2, 3)
　　
FOR JSON AUTO;
　　现在SELECT语句将会从Employee表以嵌套对象将数据返回为JSON：
[　　
   {
　　
      "FirstName":"Terri",
　　
      "MiddleName":"Lee",
　　
      "LastName":"Duffy",
　　
      "e":[
　　
         {
　　
            "BirthDate":"1971-08-01",
　　
            "NationalIDNumber":"245797967",
　　
            "LoginID":"adventure-works\\terri0"
　　
         }
　　
      ]
　　
   },
　　
   {
　　
      "FirstName":"Roberto",
　　
      "LastName":"Tamburello",
　　
      "e":[
　　
         {
　　
            "BirthDate":"1974-11-12",
　　
            "NationalIDNumber":"509647174",
　　
            "LoginID":"adventure-works\\roberto0"
　　
         }
　　
      ]
　　
   }
　　
]
　　如你所见，我们有两个e数组，嵌入在外层对象中。我们可以继续调整SELECT语句来尝试获得更加接近我们想要的JSON结果，或者我们可以使用PATH模式代替，它给予我们对于JSON格式化输出的完全控制。对于最基本的SELECT语句，你可能想使用PATH模式。
　　PATH模式
　　为了使用PATH模式，我们开始在FOR JSON从句中指定PATH，而不是AUTO，如下示例：
SELECT p.FirstName, p.MiddleName, p.LastName,　　
e.BirthDate, e.NationalIDNumber, e.LoginID
　　
FROM HumanResources.Employee e INNER JOIN Person.Person p
　　
ON e.BusinessEntityID = p.BusinessEntityID
　　
WHERE e.BusinessEntityID in (2, 3)
　　
FOR JSON PATH;
　　当我们切换到PATH模式，数据库引擎格式化我们的结果，并以两个对象值在一个数组中返回数据：
[　　
   {
　　
      "FirstName":"Terri",
　　
      "MiddleName":"Lee",
　　
      "LastName":"Duffy",
　　
      "BirthDate":"1971-08-01",
　　
      "NationalIDNumber":"245797967",
　　
      "LoginID":"adventure-works\\terri0"
　　
   },
　　
   {
　　
      "FirstName":"Roberto",
　　
      "LastName":"Tamburello",
　　
      "BirthDate":"1974-11-12",
　　
      "NationalIDNumber":"509647174",
　　
      "LoginID":"adventure-works\\roberto0"
　　
   }
　　
]
　　以这种方式使用PATH模式是相当直接的；然而，这是PATH的最基本用法。该模式让我们有更多定制。例如，我们可以控制数据库引擎如何通过指定列别名用于定义结构来嵌套JSON输出，如下所示SELECT从句：
SELECT　　
p.FirstName AS [Name.First],
　　
p.MiddleName AS [Name.Middle],
　　
p.LastName AS [Name.Last],
　　
e.BirthDate AS [PII.DOB],
　　
e.NationalIDNumber AS [PII.NatID],
　　
e.LoginID
　　
FROM HumanResources.Employee e INNER JOIN Person.Person p
　　
ON e.BusinessEntityID = p.BusinessEntityID
　　
WHERE e.BusinessEntityID in (2, 3)
　　
FOR JSON PATH;
　　在这种情况下，我们定义Name对象，包含First，Middle和Last值；PII对象，包含DOB和NatID值；和LoginID名称/值对，如下结果所示：
[　　
   {
　　
      "Name":{
　　
         "First":"Terri",
　　
         "Middle":"Lee",
　　
         "Last":"Duffy"
　　
      },
　　
      "PII":{
　　
         "DOB":"1971-08-01",
　　
         "NatID":"245797967"
　　
      },
　　
      "LoginID":"adventure-works\\terri0"
　　
   },
　　
   {
　　
      "Name":{
　　
         "First":"Roberto",
　　
         "Last":"Tamburello"
　　
      },
　　
      "PII":{
　　
         "DOB":"1974-11-12",
　　
         "NatID":"509647174"
　　
      },
　　
      "LoginID":"adventure-works\\roberto0"
　　
   }
　　
]
　　在某些情况下，你想添加一个单一的，顶层元素到JSON输出中作为root元素。为了这么做，你必须指定它为FOR JSON从句的一部分，如下示例：
SELECT　　
p.FirstName AS [Name.First],
　　
p.MiddleName AS [Name.Middle],
　　
p.LastName AS [Name.Last],
　　
e.BirthDate AS [PII.DOB],
　　
e.NationalIDNumber AS [PII.NatID],
　　
e.LoginID
　　
FROM HumanResources.Employee e INNER JOIN Person.Person p
　　
ON e.BusinessEntityID = p.BusinessEntityID
　　
WHERE e.BusinessEntityID in (2, 3)
　　
FOR JSON PATH, ROOT('Employees');
　　为了指定root，我们添加ROOT选项到FOR JSON从句，在这里，命名root为Employees，得到如下结果：
{　　
   "Employees":[
　　
      {
　　
         "Name":{
　　
            "First":"Terri",
　　
            "Middle":"Lee",
　　
            "Last":"Duffy"
　　
         },
　　
         "PII":{
　　
            "DOB":"1971-08-01",
　　
            "NatID":"245797967"
　　
         },
　　
         "LoginID":"adventure-works\\terri0"
　　
      },
　　
      {
　　
         "Name":{
　　
            "First":"Roberto",
　　
            "Last":"Tamburello"
　　
         },
　　
         "PII":{
　　
            "DOB":"1974-11-12",
　　
            "NatID":"509647174"
　　
         },
　　
         "LoginID":"adventure-works\\roberto0"
　　
      }
　　
   ]
　　
}
　　如果你将结果与之前的示例比较，将会看到外层元素从一个数组改变为一个只包含Employees属性的对象。Employees值现在之前示例中的外层元素。
　　你或许也注意到了第二个employee，Roberto，没有包含中间名。那是因为在原表中MiddleName列为null。默认，数据库引擎不包含值为null的JSON元素。然而，你可以通过添加INCLUDE_NULL_VALUES选项到FOR JSON来重写该行为，如下SELECT语句所示：
SELECT　　
p.FirstName AS [Name.First],
　　
p.MiddleName AS [Name.Middle],
　　
p.LastName AS [Name.Last],
　　
e.BirthDate AS [PII.DOB],
　　
e.NationalIDNumber AS [PII.NatID],
　　
e.LoginID
　　
FROM HumanResources.Employee e INNER JOIN Person.Person p
　　
ON e.BusinessEntityID = p.BusinessEntityID
　　
WHERE e.BusinessEntityID in (2, 3)
　　
FOR JSON PATH, ROOT('Employees'), INCLUDE_NULL_VALUES;
　　现在结果将会显示Roberto的中间名为null，通过分配null值给Middle属性：
{　　
   "Employees":[
　　
      {
　　
         "Name":{
　　
            "First":"Terri",
　　
            "Middle":"Lee",
　　
            "Last":"Duffy"
　　
         },
　　
         "PII":{
　　
            "DOB":"1971-08-01",
　　
            "NatID":"245797967"
　　
         },
　　
         "LoginID":"adventure-works\\terri0"
　　
      },
　　
      {
　　
         "Name":{
　　
            "First":"Roberto",
　　
            "Middle":null,
　　
            "Last":"Tamburello"
　　
         },
　　
         "PII":{
　　
            "DOB":"1974-11-12",
　　
            "NatID":"509647174"
　　
         },
　　
         "LoginID":"adventure-works\\roberto0"
　　
      }
　　
   ]
　　
}
　　当然，使用该从句时有些需要考虑的，参见SQL Server 2016文档。与此同时，让我们看看如何转换JSON片段到传统的行集数据。
　　使用OPENJSON函数转换JSON到行集数据
　　
　　为了返回JSON片段为行集数据，我们使用OPENJSON行集函数转换数据为关系型格式。该函数返回三个值：
　　. key:对象中的属性名或者数组中元素的索引。
　　. value:对象中的属性值或者通过指定索引的数组元素值。
　　. type:值的数据类型，以数字表示，如下表所描述：
　　Numeric value
　　Data type
　　0
　　null
　　1
　　string
　　2
　　int
　　3
　　true or false
　　4
　　array
　　5
　　object
　　为了测试OPENJSON函数如何工作，让我们分配一个JSON片段到一个变量，然后使用该函数去调用这个变量，如下示例所示：
DECLARE @json NVARCHAR(MAX) = N'　　
{
　　
"FirstName":null,
　　
"LastName":"Duffy",
　　
"NatID":245797967,
　　
"Current":false,
　　
"Skills":["Dev","QA","PM"],
　　
"Region":{"Country":"Canada","Territory":"North America"}
　　
}';
　　
SELECT * FROM OPENJSON(@json);
　　这个JSON片段包含对每个数据类型包含一个属性的单一对象。SELECT语句在FROM从句中使用OPENJSON行集函数来获取JSON数据为行集，显示为如下结果：
　　key
　　value
　　type
　　FirstName
　　NULL
　　0
　　LastName
　　Duffy
　　1
　　NatID
　　245797967
　　2
　　Current
　　false
　　3
　　Skills
　　["Dev","QA","PM"]
　　4
　　Region
　　{"Country":"Canada","Territory":"North America"}
　　5
　　注意到结果中的type列对于每个值标识了数据类型。如所期待的，该列显示Skills的值为一个数组，将所有的数组元素包含在一行结果中。同样看到Region的值，是一个对象。该行包含对象中的所有属性。
　　在某些情况下，你会只想返回key和value列，因此你会指定这些列在SELECT列表中：
SELECT [key], value　　
FROM OPENJSON(@json);
　　注意到你必须限定key列，因为微软选择返回一个列表也是一个T-SQL保留字。如下表所示，结果只包含两列：
　　key
　　value
　　FirstName
　　NULL
　　LastName
　　Duffy
　　NatID
　　245797967
　　Current
　　false
　　Skills
　　["Dev","QA","PM"]
　　Region
　　{"Country":"Canada","Territory":"North America"}
　　现在让我们继续一个更为复杂的JSON片段，我们将在这篇文章中作为保留示例：
{　　
   "Employees":[
　　
      {
　　
         "Name":{
　　
            "First":"Terri",
　　
            "Middle":"Lee",
　　
            "Last":"Duffy"
　　
         },
　　
         "PII":{
　　
            "DOB":"1971-08-01",
　　
            "NatID":"245797967"
　　
         },
　　
         "LoginID":"adventure-works\\terri0"
　　
      },
　　
      {
　　
         "Name":{
　　
            "First":"Roberto",
　　
            "Middle":null,
　　
            "Last":"Tamburello"
　　
         },
　　
         "PII":{
　　
            "DOB":"1974-11-12",
　　
            "NatID":"509647174"
　　
         },
　　
         "LoginID":"adventure-works\\roberto0"
　　
      }
　　
   ]
　　
}
　　这里显示的JSON输出来自于上一个示例中的相应部分产生的。你重新调用，数据库引擎实际上会比这里显示的更不易阅读的格式输出JSON，当分配JSON到一个变量时它更容易工作。因此这就是我们将会在接下来示例中采取的措施：
DECLARE @json NVARCHAR(MAX) = N'{"Employees":[{"Name":{"First":"Terri","Middle":"Lee","Last":"Duffy"},"PII":{"DOB":"1971-08-01","NatID":"245797967"},"LoginID":"adventure-works\\terri0"},{"Name":{"First":"Roberto","Middle":null,"Last":"Tamburello"},"PII":{"DOB":"1974-11-12","NatID":"509647174"},"LoginID":"adventure-works\\roberto0"}]}';　　如果你计划去尝试下一批示例，你将对每个使用变量定义，避免了所有当通过分析器运行结果时获得空格。现在让我们使用OPENJSON函数去变量中转换JSON：
SELECT [key], value　　
FROM OPENJSON(@json);
　　这个示例最基本的使用OPENJSON，不用定义其他参数。结果，SELECT语句对于Employees数组只返回单一行，如下表所示：
　　key
　　value
　　Employees
　　[{"Name":{"First":"Terri","Middle":"Lee","Last":"Duffy"},"PII":{"DOB":"1971-08-01","NatID":"245797967"},"LoginID":"adventure-works\\terri0"},{"Name":{"First":"Roberto","Middle":null,"Last":"Tamburello"},"PII":{"DOB":"1974-11-12","NatID":"509647174"},"LoginID":"adventure-works\\roberto0"}]
　　为了更好的控制我们的结果，我们需要传递第二个参数到OPENJSON函数。该参数是一个JSON路径，命令数据库引擎如何解析数据。例如，下面的路径命令数据库引擎基于Employees属性返回数据：
SELECT [key], value　　
FROM OPENJSON(@json, '$.Employees');
　　当你指定一个JSON路径时，你使用一个美元符号（$）开始以表明这个条目存在于当前的上下文中。你然后指定一个或更多元素，因为它们分等级的出现在JSON片段中，使用阶段分割元素。在这种情况下，路径只指定了根元素，Employees，将结果显示在如下表中：
　　key
　　value
　　0
　　{"Name":{"First":"Terri","Middle":"Lee","Last":"Duffy"},"PII":{"DOB":"1971-08-01","NatID":"245797967"},"LoginID":"adventure-works\\terri0"}
　　1
　　{"Name":{"First":"Roberto","Middle":null,"Last":"Tamburello"},"PII":{"DOB":"1974-11-12","NatID":"509647174"},"LoginID":"adventure-works\\roberto0"}
　　这次，我们对Employees数组中的每个元素获得一行。如果我们想就爱那个结果进一步分割，我们必须逐步减少层次结构。例如，参考Employees数组中的一个元素，我们必须指定该元素的索引，因为它存在于数组中。一个数组的索引是以零开始的，意味着索引计数以0开始，因此如果我们想获取Employees数组中的第一个元素，我们必须在root名称后在中括号中指定0，显示为如下语句：
SELECT [key], value　　
FROM OPENJSON(@json, '$.Employees[0]');
　　Employees数组中的第一个元素是一个包含三个属性的JSON对象，那是SELECT语句返回的，显示为如下结果：
　　key
　　value
　　Name
　　{"First":"Terri","Middle":"Lee","Last":"Duffy"}
　　PII
　　{"DOB":"1971-08-01","NatID":"245797967"}
　　LoginID
　　adventure-works\terri0
　　因为前两个值是对象，这些对象的完整内容返回。然后，我们可以通过指定对象名称只返回这些对象中的一个：
SELECT [key], value　　
FROM OPENJSON(@json, '$.Employees[0].Name');
　　现在SELECT语句只返回Name对象中的三个属性：
　　key
　　value
　　First
　　Terri
　　Middle
　　Lee
　　Last
　　Duffy
　　我们看到的OPENJSON示例，当返回数据为行集时，使用了默认的架构，但是有些限制有关如何控制结果。幸运的是，OPENJSON函数也让我们添加一个WITH从句到我们的SELECT语句为了定义一个显式架构。在下面的示例中，该架构格式化我们的数据，因此我们可以很容易对每个employee看到详细信息：
SELECT *　　
FROM OPENJSON(@json, '$.Employees')
　　
WITH([Name.First] NVARCHAR(25), [Name.Middle] NVARCHAR(25),
　　
[Name.Last] NVARCHAR(25), [PII.DOB] DATE, [PII.NatID] INT);
　　WITH从句指定每个列，使用关联到原始JSON的名字。例如，Name.First列返回employee的名字。该列名称基于Name对象的First属性。对于每个列，我们也提供了一个T-SQL数据类型。SELECT语句现在返回结果显示在如下表中：
　　Name.First
　　Name.Middle
　　Name.Last
　　PII.DOB
　　PII.NatID
　　Terri
　　Lee
　　Duffy
　　1971-08-01
　　245797967
　　Roberto
　　NULL
　　Tamburello
　　1974-11-12
　　509647174
　　如果我们想定义更多可读的列名称，我们可以替代创建列定义，每个包含新名称，跟随数据类型和路径参照，如下示例所示：
SELECT *　　
FROM OPENJSON(@json, '$.Employees')
　　
WITH(FirstName NVARCHAR(25) '$.Name.First',
　　
MiddleName NVARCHAR(25) '$.Name.Middle',
　　
LastName NVARCHAR(25) '$.Name.Last',
　　
BirthDate DATE '$.PII.DOB',
　　
NationalID INT '$.PII.NatID');
　　注意到，对于路径，我们不需要参照Employees数组自身。让我们关注OPENJSON函数。但是我们仍需要指定美元符号去显示当前上下文。我们然后跟随Name或PII对象名称，然后属性名称。SELECT语句现在返回结果显示在如下表中：
　　FirstName
　　MiddleName
　　LastName
　　BirthDate
　　NationalID
　　Terri
　　Lee
　　Duffy
　　1971-08-01
　　245797967
　　Roberto
　　NULL
　　Tamburello
　　1974-11-12
　　509647174
　　接下来的示例将会给出一个基本的关于如何将JSON片段转换为行集数据的方法。再一次，参考SQL Server 2016文档去获取更多关于如何使用OPENJSON函数的规范。
　　在SQL Server 2016中更多JSON函数
　　
　　除了OPENJSON，SQL Server 2016包含其他一些函数用于处理JSON数据。我们将学习如何使用ISJSON，JSON_VALUE和JSON_QUERY函数。
　　ISJSON
　　ISJSON函数让你测试一个字符串是否正确的以JSON格式化。这是一个特别重要的函数，考虑到SQL Server 2016不支持JSON数据类型。至少这种方式，你有方法验证你的数据。
　　如果一个字符串是有效的JSON值，ISJSON函数返回1，否则返回0。唯一的异常是，如果字符串为null，此时函数返回null。下面的SELECT语句测试普遍存在的@json变量来验证是否有效：
SELECT CASE　　
WHEN ISJSON(@json) > 0
　　
THEN 'The variable value is JSON.'
　　
ELSE 'The variable value is not JSON.'
　　
END;
　　如我们所希望的，SELECT语句返回如下结果：
The variable value is JSON.　　现在让我们通过不赋值标记Age元素来传递无效的JSON到文本：
DECLARE @json2 NVARCHAR(MAX) = N'　　
{"First":"Terri","Middle":"Lee","Last":"Duffy","Age"}';
　　
SELECT CASE
　　
WHEN ISJSON(@json2) > 0
　　
THEN 'The variable value is JSON.'
　　
ELSE 'The variable value is not JSON.'
　　
END;
　　如我们所希望的，我们得到第二种结果：
The variable value is not JSON.　　
　　JSON_VALUE
　　SQL Server 2016中另一个手头JSON相关的函数是JSON_VALUE，让我们从一个JSON片段中抽取一个标量值，如下示例所示：
SELECT JSON_VALUE(@json, '$.Employees[0].Name.First');　　JSON_VALUE函数传入两个参数。第一个是JSON自身，第二个是一个定义了我们想获取的元素的值的路径。在这里，路径指定了Name对象的First属性，它是Employees数组的第一个元素的一部分。如我们所期待，SELECT语句返回了值Terri。
　　我们可以容易的对第二个employee返回NatID值。
SELECT JSON_VALUE(@json, '$.Employees[1].PII.NatID');　　现在这个SELECT语句返回509647174。假设，我们而是去获取一些非标量值。例如，如下路径对第二个employee只指定PII对象：
SELECT JSON_VALUE(@json, '$.Employees[1].PII');　　这次，SELECT语句返回一个null值。默认，如果路径不存在或者不匹配当前情况，数据库引擎返回null值。在这个例子中，我们指定了一个不能返回标量值的元素，因此数据引擎返回了null值。
　　当在JSON相关表达式中指定一个路径，你可以通过使用lax或strict选项处理路径来控制结果。lax选项是默认值，如果没有指定，会被隐式指定，意思是如果一个问题触发，数据库引擎返回null值。例如，如下路径限制包含lax选项：
SELECT JSON_VALUE(@json, 'lax $.Employees[1].PII');　　输出语句再次返回null值，因为我们指定了一个不能返回标量值的元素。我们替代指定了strict选项，这这里，如果问题出现，数据库引擎将报错：
SELECT JSON_VALUE(@json, 'strict $.Employees[1].PII');　　这次我们收到了非常不一样的结果：
Property cannot be found in specified path.　　
　　JSON_QUERY
　　另一个JSON相关的工具是JSON_QUERY函数，从一个JSON片段中抽取了一个对象或数组。例如，以下SELECT语句对第二个employee获取PII对象：
SELECT JSON_QUERY(@json, 'strict $.Employees[1].PII');　　像JSON_VALUE函数一样，JSON_QUERY函数传入两个参数：JSON源和包含要抽取数据的路径。SELECT语句返回的结果如下：
{"DOB":"1974-11-12","NatID":"509647174"}　　如果我们想去返回一个Employees数组，我们只需要指定$.Employees作为我们的路径：
SELECT JSON_QUERY(@json, 'strict $.Employees');　　现在SELECT语句只返回JSON片段中的内容：
[{"Name":{"First":"Terri","Middle":"Lee","Last":"Duffy"},"PII":{"DOB":"1971-08-01","NatID":"245797967"},"LoginID":"adventure-works\\terri0"},{"Name":{"First":"Roberto","Middle":null,"Last":"Tamburello"},"PII":{"DOB":"1974-11-12","NatID":"509647174"},"LoginID":"adventure-works\\roberto0"}]　　
　　总结：JSON和SQL Server 2016
　　
　　本文开始让你在SQL Server中使用JSON数据。然而，如你所见，JSON支持不如XML支持那样完善。如果你也正在维护其他数据库管理系统，你会很快发现在SQL Server 2016中的JSON属性还有很多工作要迎头赶上在其他产品中已经实现的。
　　即便如此，SQL Server 2016聊胜于无，JSON支持被证明是很实用的。事实上，对一些组织，在SQL Server 2016中已经实现的属性将足够满足他们的需求。首先，JSON相关的功能简单、易用，你应该相对不用什么烦恼就能应用到你的工作中。