mgm6608美高梅app下载-mgm集团美高梅登录

mgm6608美高梅app下载以独家报道、社区、博客和专题策划等方式引领产业潮流,mgm集团美高梅登录为足球爱好者以及体育爱好者提供足球上网,让你无后顾之忧!,拥有多位香港乐坛红星。

最近在项目中很多地方字段都是存一个,定义和

日期:2020-02-26编辑作者:mgm集团美高梅登录

SQL Server 中 JSON_MODIFY 的使用

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

Intro

SQL Server 提供了内置函数,用于查询和更新JSON数据,分析JSON文本,如图:

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

mgm6608美高梅app下载 1

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

一,定义和验证JSON数据

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

使用nvarchar存储JSON文本数据,通过函数 ISJSON(expression) 验证JSON数据是否有效。

示例

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)

直接来看示例:

ISJSON 函数的格式是: ISJSON ( expression ) ,返回1,表示字符串是JSON数据;返回0,表示字符串不是JSON数据;返回NULL,表示 expression是NULL;

-- 适用 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);

二,JSON 数据的PATH 表达式

输出结果如下图:

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

More

1,Path Mode

注意:

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

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

2,Path 表达式

Reference

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

总结

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

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

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

{ "people":  
  [  
    { "name": "John", "surname": "Doe" },  
    { "name": "Jane", "surname": null, "active": true }  
  ]  
} 

Path表达式查询的数据是:

  • $:表示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;

三,通过Path查询JSON数据

1,查询标量值(JSON_VALUE)

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

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

select
  json_value(@json, '$.type') as type,
  json_value(@json, '$.info.type') as info_type,
  json_value(@json, '$.info.address.town') as town,
  json_value(@json, '$.info.tags[0]') as tag

mgm6608美高梅app下载 2

2,返回JSON数据(JSON_QUERY)

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

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

select
    json_query(@json, '$') as json_context,
    json_query(@json, '$.info') as info,
    json_query(@json, '$.info.address') as info_address,
    json_query(@json, '$.info.tags') as info_tags

mgm6608美高梅app下载 3

四,通过Path修改JSON数据

使用 JSON_MODIFY ( expression , path , newValue ) 修改JSON数据中的属性值,并返回修改之后的JSON数据,该函数修改JSON数据的流程是:

  • 修改现有的属性:按照参数path从JSON数据中找到指定的属性,将该属性的Value修改为参数newValue,返回值是修改之后的JSON数据;
  • 新增新的键值对(Key:Value pair):如果JSON数据中不存在指定的属性,那么按照参数Path,在指定的路径上新增键值对;
  • 删除键值对(Key:Value pair):如果参数newValue的值是NULL,那么表示从JSON数据中删除指定的属性;
  • append 关键字:用于从JSON数组中,追加一个元素;

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

declare @info nvarchar(100) = '{"name":"john","skills":["c#","sql"]}'  
-- update name  
set @info = json_modify(@info, '$.name', 'mike')  
-- insert surname  
set @info = json_modify(@info, '$.surname', 'smith')  
-- delete name  
set @info = json_modify(@info, '$.name', null)  
-- add skill  
set @info = json_modify(@info, 'append $.skills', 'azure')  

mgm6608美高梅app下载 4

五,将JSON数据转换为关系表

OPENJSON函数是一个行集函数(RowSet),能够将JSON数据转换为关系表,

mgm6608美高梅app下载 5mgm6608美高梅app下载 6

OPENJSON( jsonExpression [ , path ] )  
[  
   WITH (   
      colName type [ column_path ] [ AS JSON ]  
   [ , colName type [ column_path ] [ AS JSON ] ]  
   [ , . . . n ]   
      )  
] 

View Code

  • 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三列;

1,示例,从JSON数据中,以关系表方式呈现数据

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

SELECT info_type,info_address,tags
FROM OPENJSON(@json, '$.info') 
with 
(
info_type tinyint 'lax $.type',
info_address nvarchar(max) 'lax $.address' as json,
tags nvarchar(max) 'lax $.tags' as json
)

2,OpenJSON 函数的另外一个功能是遍历数组,为数组中的每一个元素产生一个数据行

When you use OPENJSON with an explicit schema, the function returns a table with the schema that you defined in the WITH clause. In the WITH clause, you define columns, their types, and the paths of the source properties for each column.

  • 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.

mgm6608美高梅app下载 7mgm6608美高梅app下载 8

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

View Code

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

mgm6608美高梅app下载 9mgm6608美高梅app下载 10

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

View Code

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

通过For JSON  Auto/Path,将关系表数据存储为JSON格式,

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

示例,有表:dt_json,存储以下数据:

mgm6608美高梅app下载 11

1,以Auto 模式生成JSON格式

select id,
    name,
    category
from dbo.dt_json
for json auto,root('json')

本文由mgm6608美高梅app下载发布于mgm集团美高梅登录,转载请注明出处:最近在项目中很多地方字段都是存一个,定义和

关键词:

【mgm集团美高梅登录】Azure SQL数据仓库

2 具体内容 2.1 使用范围 SQL Server ;Azure SQL数据库;Azure SQL数据仓库;并行数据仓库 2.2 语法 是SQLServer数据库引擎评估...

详细>>

②界定标识符,datetime 秒的小数部分精度的舍入

废话少说,直接上SQL代码,下面这个查询语句为什么将2008-11-27的记录查询出来了呢?这个是同事遇到的一个问题,个...

详细>>

服务和连接的外围应用配置器,3.关闭防火墙

DataGrip支持几乎所有主流的关系数据库产品,如DB2、Derby、H2、MySQL、Oracle、PostgreSQL、SQLServer、Sqllite及Sybase等,并且提...

详细>>

基本配置文件,2.1 解压zip包到安装目录

mysql服务8.0.14安装(通用),供大家参考,具体内容如下 1、下载zip安装包: 1. 解压zip包到安装目录 安装环境:windows安...

详细>>