金沙澳门官网网址-澳门金沙官网登录

金沙澳门官网网址拥有大量的原创内容,澳门金沙官网登录提供最权威、快速、全面、专业的报道,所以金沙澳门官网网址注册送彩金就相当于直接送现金了,享受最尊贵的贵宾服务。

定义和验证JSON数据,需要了解一下怎么在SQL Se

2020-01-09 01:22栏目:金沙澳门官网
TAG:

JSON支持适用于 SQL Server 2016 及以上版本 和 Azure SQL Database。

使用 JSON_金沙澳门官网网址 ,VALUE(expression , path ) 函数,从JSON数据,根据Path 参数返回标量值,返回的数据是宽字符类型,最大值Nvarchar(4000);如果必须返回大于nvarchar(4000)的数据,使用OpenJson行集函数。

SQL Server 中 JSON_MODIFY 的使用

在查询时,经常会返回JSON数组,使用[index]来遍历数组元素,数组下标从0开始,例如,以下JSON数组,及其查询示例:

直接来看示例:

select id as 'book.id',
    name as 'book.name',
    category as 'product.category'
from dbo.dt_json
for json path,root('json')
-- 适用 append 在数组中追加元素PRINT JSON_MODIFY(@testJsonValue, 'append $.skills', 'Azure');-- 无效用法,这样会把属性更新为字符串PRINT JSON_MODIFY(@testJsonValue, '$.skills', '["C#","T-SQL","Azure"]');-- 修改数组PRINT JSON_MODIFY(@testJsonValue, '$.skills', JSON_QUERY('["C#","T-SQL","Azure"]'));-- 修改数组某个元素PRINT JSON_MODIFY(@testJsonValue, '$.skills[0]', 'CSharp');-- 增加属嵌套PRINT JSON_MODIFY(@testJsonValue, '$.Likes', JSON_QUERY('{"Girl": "Alice"}'));PRINT JSON_MODIFY(@testJsonValue, '$.Likes', JSON_QUERY('{"Animals": ["Dog","Cat"]}'));-- 增加 bool 属性PRINT JSON_MODIFY(@testJsonValue, '$.ShowAwardsAfterQuizEnds', CONVERT(BIT, 'false'));PRINT JSON_MODIFY(@testJsonValue, '$.ShowAwardsAfterQuizEnds', CONVERT(BIT, 1));-- 增加 int 属性PRINT JSON_MODIFY(@testJsonValue, '$.TotalWinnersCount', CONVERT(INT, '20'));PRINT JSON_MODIFY(@testJsonValue, '$.TotalWinnersCount', 20);-- 删除属性PRINT JSON_MODIFY(@testJsonValue, '$.name', NULL);

金沙澳门官网网址 1金沙澳门官网网址 2

输出结果如下图:

[{"name":"table_1","object_id":27147142,"type":"U "},
{"name":"table_2","object_id":87671360,"type":"U "},
{"name":"table_3","object_id":91147370,"type":"U "}]

Intro

例如,有如下关系表,字段category包含JSON数据:

最近在项目中很多地方字段都是存一个 JSON 字符串,尤其是针对一些有很多不确定的属性的数据来说,这样的信息我们一般都放在一个 Extra 之类的字段保存一个 JSON 字符串,由于不确定性很大,很有可能会遇到要修改这个 JSON 字符串结构的问题,在修改 JSON 对象的某一个值和修改 JSON 的结构时都会用到 JSON_MODIFY,今天就来介绍一下 JSON_MODIFY 的使用

金沙澳门官网网址 3金沙澳门官网网址 4

示例

使用 JSON_QUERY ( expression [ , path ] ) 函数,根据Path 参数,返回JSON 数据(JSON fragment);参数path是可选的(optional),如果不指定option参数,那么默认的path是$,即,返回整个JSON数据。

注意:

金沙澳门官网网址 5

如果要更新的字段没有是一个 JSON 对象或是一个数组,那么需要使用 JSON_QUERY ,否则更新的对应的属性就成了字符串了。 如果是要删除某一个属性,把某一个属性更新为 NULL 即可 如果要增加一个 bool 类型的属性,需要把对应的值转换为 BIT 类型

  • For each element in the array in the input expression, OPENJSON generates a separate row in the output table.

  • For each property of the array elements specified by using the colName type column_path syntax, OPENJSON converts the value to the specified type and populates a cell in the output table.

Reference

JSON Path Expressions (SQL Server)

SQL Server 从 2016 开始支持了一些 JSON操作,最近的项目里也是好多地方字段直接存成了 JSON,需要了解一下怎么在SQL Server 中操作 JSON.

示例,对JSON数据进行update,insert,delete和追加数据元素

以上所述是小编给大家介绍的SQL Server 中 JSON_MODIFY 的使用,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持!如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!

JSON文本不是内置的数据类型,没有专门的JSON索引,但是,可以通过创建计算列和标准B-Tree索引提高查询JSON数据的性能,避免全表扫描(Full Table Scan),通过索引计算列,间接实现对JSON进行查找。

More

1,使用Path模式,控制JSON结构的Path(层次)

总结

JSON是一个非常流行的,用于数据交换的文本数据(textual data)格式,主要用于Web和移动应用程序中。JSON 使用“键/值对”(Key:Value pair)存储数据,能够表示嵌套键值对和数组两种复杂数据类型,JSON仅仅使用逗号(引用Key)和中括号(引用数组元素),就能路由到指定的属性或成员,使用简单,功能强大。在SQL Server 2016版本中支持JSON格式,使用Unicode字符类型表示JSON数据,并能对JSON数据进行验证,查询和修改。推荐一款JSON验证和格式化的在线工具:json formatter。

SET @json = N'{"Orders":   
  {"OrdersArray":  
    [  
      {  
        "Order": {  
          "Number":"SO43659",  
          "Date":"2011-05-31T00:00:00"  
        },  
        "AccountNumber":"AW29825",  
        "Item": {  
          "Price":2024.9940,  
          "Quantity":1  
        }  
      },  
      {  
        "Order": {  
          "Number":"SO43661",  
          "Date":"2011-06-01T00:00:00"  
        },  
        "AccountNumber":"AW73565",  
        "Item": {  
          "Price":2024.9940,  
          "Quantity":3  
        }  
      }  
    ]  
  }  
}'  

SELECT t.* 
FROM  
OPENJSON ( @json, '$.Orders.OrdersArray' )  
WITH (   
             Number   varchar(200)   '$.Order.Number',  
             Date     datetime       '$.Order.Date',  
             Customer varchar(200)   '$.AccountNumber',  
             Quantity int            '$.Item.Quantity',  
             [Order]  nvarchar(MAX)  AS JSON  
) as t

JSON Data (SQL Server)

Format Nested JSON Output with PATH Mode (SQL Server)

OPENJSON( jsonExpression [ , path ] )  
[  
   WITH (   
      colName type [ column_path ] [ AS JSON ]  
   [ , colName type [ column_path ] [ AS JSON ] ]  
   [ , . . . n ]   
      )  
] 
select top 3 t.name
    ,o.object_id
    ,o.type
from sys.objects o 
inner join sys.tables t 
    on o.object_id=t.object_id
for json auto

返回的数据格式是

 

declare @json nvarchar(max)
set @json = 
N'{
    "info":{  
      "type":1,
      "address":{  
        "town":"bristol",
        "county":"avon",
        "country":"england"
      },
      "tags":["sport", "water polo"]
   },
   "type":"basic"
}'

select isjson(@json)

金沙澳门官网网址 6

金沙澳门官网网址 7金沙澳门官网网址 8

索引JSON数据的Workaround是:为查询条件(Filter)创建计算列,使用persisted属性持久存储;在计算列上创建索引,使用包含列(Include)包含特定的字段,以避免键值查找(Key Lookup),提高索引查找的性能。

1,以Auto 模式生成JSON格式

七,索引JSON数据

for json path,without_array_wrapper

在查询时,Table_2的JsonData字段是个Json数据,需要嵌套到另一个JSON中,例如:[{"UnitPrice":12, "OrderQty":1}],如果在外层JSON结构中,嵌套一个内层的JSON结构:

Format Query Results as JSON with FOR JSON (SQL Server)

例如,有如下JSON 数据,通过Path表达式,能够路由到JSON的各个属性:

3,OpenJSON 函数搭配Apply使用,为表中的JSON数据转换成关系表形式

2,嵌套JSON结构

JSON Functions (Transact-SQL)

  • $:代表整个JSON 数据的内容;
  • 逗号 . :表示JSON对象的成员,也叫做,字段(Field),或Key;
  • 中括号 [] :表示数组中的元素,元素的起始位置是0;
  • Key Name:键的名字,通过Key Name引用对应的Value;如果Key Name中包含空格,$,逗号,中括号,使用双引号;

返回的JSON结构如下,满足:

1,空JSON

参考文档:

1,查询标量值(JSON_VALUE)

[
  {
    "Id": 12,
    "JsonData": "[{"UnitPrice":12, "OrderQty":1}]"
  }
]

Path表达式查询的数据是:

select t.*,sl.result,sl.time
from [dbo].[WebPages] sl 
cross apply openjson(JSON_QUERY(Parameters,'$.CategoryList'))
with
(
    ID varchar(64) '$.ID',
    name varchar(64) '$.Name',
    Type varchar(64) '$.Type'
)
 as t
where sl.action='New Product' and t.Type in('Blogs','Forums')
order by sl.time desc

金沙澳门官网网址 9金沙澳门官网网址 10

六,将关系表数据以JSON格式存储

  • path 参数:也叫table path,指定关系表在JSON数据中的路径;
  • column_path 参数:基于path参数,指定每个column在关系表JSON中的路径,应总是显式指定column path;
  • AS JSON 属性:如果指定AS JSON属性,那么 column的数据类型必须定义为nvarchar(max),表示该column的值是JSON数据;如果不指定AS JSON属性,那么该Column的值是标量值;
  • with 选项:指定关系表的Schema,应总是指定with选项;如果不指定with 选项,那么函数返回key,value和type三列;

2,返回JSON数据(JSON_QUERY)

Path是访问JSON数据的途径,有四种运算符:

在Path 表达式的开始,可以通过lax 或 strict 关键字显式声明Path Mode,如果不声明,默认的Path Mode是lax。在lax 模式下,如果path表达式出错,那么JSON函数返回NULL。在strict模式下,如果Path表达式出错,那么JSON函数抛出错误;

  • $:表示JSON的内容,是最外层大括号中的所有Item,本例是一个people数组,数组的下标是从0开始的;
  • $.people[0]:表示people数组的第一元素:{ "name": "Jane", "surname": null, "active": true }
  • $.people[0].name :从people数组的第一个元素中,查询Key是Name的Item对应的数据,本例是John;
  • $.people[1].surname:people数组中部存在surname 字段,由于该Path 表达式没有声明Path Mode,默认值是lax,当Path表达式出现错误时,返回NULL;

2,以Path模式生成JSON格式,推荐使用path模式,特别是在字段来源于多个表的情况下,控制JSON的格式**

[
{...}
]
--path expression
lax $[0]
  • Auto 模式:根据select语句中column的顺序,自动生成JSON数据的格式;
  • Path 模式:使用column name的格式来生成JSON数据的格式,column name使用逗号分隔(dot-separated)表示组-成员关系;

金沙澳门官网网址 11

View Code

八,JSON查询技巧

二,JSON 数据的PATH 表达式

Path 表达式分为两部分:Path Mode和Path,Path Mode是可选的(optional),有两种模式:lax和strict。

版权声明:本文由金沙澳门官网网址发布于金沙澳门官网,转载请注明出处:定义和验证JSON数据,需要了解一下怎么在SQL Se