设为首页 收藏本站
查看: 1362|回复: 0

[经验分享] SQL Server 2016 JSON支持

[复制链接]

尚未签到

发表于 2018-6-24 13:15:19 | 显示全部楼层 |阅读模式
  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相关的功能简单、易用,你应该相对不用什么烦恼就能应用到你的工作中。

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-530073-1-1.html 上篇帖子: windows server 2016U盘安装提示sources中的install.wim损坏 下篇帖子: 卸载SQL Server 2016
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表