首 页  -  技术分享  - 在Sql Server 2016中使用For Json子句把数据作为json格式导出

在Sql Server 2016中使用For Json子句把数据作为json格式导出

分享者:邢政     2016-01-27

使用for json子句把查询结果作为json字符串导出,将作为sql server 2016中首先可用的一个特性。如果你熟悉for xml子句,那么将很容易理解for json:

select ccolumn, expression, column as alias
    from table1, table2, table3
    for json [auto | path]

如果你把for json子句添加到T-SQL Select查询语句的最后,SQL Server将会把结果格式化为JSON字符串之后在返回到客户端。每一行数据将会格式化为一个json对象,每一个数据字段将会成为行对象的值,列名或者列的别名会作为行对象的键。我们有两种类型的for json子句:

  • FOR JSON Path,通过列名或者列别名来定义JSON对象的层次结构,列别名中可以包含“.”,JSON的成员层次结构将会与别名中的层次结构保持一致。
    这个特性非常类似于早期SQL Server版本中的For Xml Path子句,可以使用斜线来定义xml的层次结构。
  • FOR JSON Auto,自动按照查询语句中使用的表结构来创建嵌套的JSON子数组,类似于For Xml Auto特性。

如果你用过PostgreSQL中涉及到JSON的函数和操作符,你会注意到,FOR JSON子句类等价于PostgreSQL中的JSON创建函数比如row_to_json或json_object。FOR JSON子句的主要目的是根据JSON规范把变量、列格式化为JSON对象。比如:

set @json = (select 1 as firstKey, getdate() as dateKey, @someVar as thirdKey for json path)
-- result is : {"firstKey": 1, "dateKey": "2016-06-15 11:35:21", "thirdKey": "Content of variable"}

FOR JSON子句主要应用场景:

  • 把需要返回给客户端的一组对象序列化为JSON。想象一下,在你创建JSON Web服务的时候,需要提供供应商信息及其产品信息(比如在OData服务中使用$extend选项)。你可能会查询供应商列表,把每个供应商信息格式化为JSON对象并通过额外查询来获得这个供应商的产品列表,将其转化为JSON对象数组附加到供应商对象。其他方案可能会通过链接查询来获得供应商和产品信息列表,使用客户端代码来格式化为JSON对象(若使用Entity Framework将可能产生额外查询)。使用for json子句,你可以连接这两个表进行查询,添加你想要的前缀(定义JSON层次结构),在数据库层完成JSON格式化工作。
  • 在一对多的父子表关系场景,你不想创建子表,而是想把子表的记录以JSON数组的格式存储作为父表的一列。比如你不想把SalesOrderHeader和SalesOrderDetails数据分成两个表来保存,你可以把每个订单的多个商品详情格式化为JSON数组保存到SalesOrderHeader表中的一列。