详解JSON1:使用TSQL查询数据和更新JSON数据

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

一,定义和验证JSON数据

使用nvarchar表示JSON数据,通过函数ISJSON函数验证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;

二,JSON 数据的PATH 表达式

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

1,Path Mode

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

2,Path 表达式

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

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

例如,有如下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 参数返回标量值,返回的数据是字符类型。

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

 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

四,通过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')

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

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

OPENJSON( jsonExpression [ , path ] )
[
 WITH (
  colName type [ column_path ] [ AS JSON ]
 [ , colName type [ column_path ] [ AS JSON ] ]
 [ , . . . n ]
  )
]
  • 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三列;

示例,从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
)

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

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

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

1,以Auto 模式生成JSON格式

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

返回的数据格式是

{
 "json":[
  {
   "id":1,
   "name":"C#",
   "category":"Computer"
  },
  {
   "id":2,
   "name":"English",
   "category":"Language"
  },
  {
   "id":3,
   "name":"MSDN",
   "category":"Web"
  },
  {
   "id":4,
   "name":"Blog",
   "category":"Forum"
  }
 ]
}

2,以Path模式生成JSON格式

select id as 'book.id',
 name as 'book.name',
 category as 'product.category'
from dbo.dt_json
for json path,root('json')

返回的数据格式是:

{
"json":[
{
"book":{
"id":1,
"name":"C#"
},
"product":{
"category":"Computer"
}
},
{
"book":{
"id":2,
"name":"English"
},
"product":{
"category":"Language"
}
},
{
"book":{
"id":3,
"name":"MSDN"
},
"product":{
"category":"Web"
}
},
{
"book":{
"id":4,
"name":"Blog"
},
"product":{
"category":"Forum"
}
}
]
}

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持我们。

(0)

相关推荐

  • SQL Server中将查询结果转换为Json格式脚本分享

    脚本源码: 复制代码 代码如下: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE[dbo].[SerializeJSON]( @ParameterSQL AS VARCHAR(MAX) ) AS BEGIN    DECLARE @SQL NVARCHAR(MAX) DECLARE @XMLString VARCHAR(MAX) DECLARE @XML XML DECLARE @Paramlist NVARCH

  • java使用淘宝API读写json实现手机归属地查询功能代码

    一般查询手机归属地内容应该很好用json格式保存,在网上找到了淘宝的归属地API,并下了处理json相关的jar包,做了这个手机归属地查询功能 复制代码 代码如下: package com.think.java; import java.io.BufferedReader;import java.io.InputStream;import java.io.InputStreamReader;import java.net.MalformedURLException;import java.net

  • JS 实现Json查询的方法实例

    其实很简单,我这部分代码,前一部分是简单的实现如何使用JS写模板,第二个就是具体的实现了JSON查询的一个扩展. 以后查询Json就有了利器了. 复制代码 代码如下: /*         * 定义模板函数        */        var template = function (queryArr) { var count = 0; for (var i = 0; i < queryArr.length; i++) { var e = queryArr[i]; if ($express

  • 用函数模板,写一个简单高效的 JSON 查询器的方法介绍

    JSON可谓是JavaScript的亮点,它能用优雅简练的代码实现Object和Array的初始化.同样是基于文本的数据定义,它比符号分隔更有语义,比XML更简洁.因此越来越多的JS开发中,使用它作为数据的传输和储存. JS数组内置了不少有用的方法,方便我们对数据的查询和筛选.例如我们有一堆数据: 复制代码 代码如下: var heros = [        // 名============攻=====防=======力量====敏捷=====智力====        {name:'冰室女巫

  • jsonp跨域请求数据实现手机号码查询实例分析

    本文实例讲述了jsonp跨域请求数据实现手机号码查询的方法.分享给大家供大家参考,具体如下: 前言 网上有很多开放的api,我们在本地通过ajax获取数据时,总会碰到一个问题,那就是跨域!如果不借助php等,仅仅通过js怎么解决跨域的问题呢?或许jsonp是个不错的选择. 知识准备 之前一篇<说说JSON和JSONP 也许你会豁然开朗>对jsonp已经介绍的很详细了,这里就不多介绍了,直接上实例吧 代码: <!DOCTYPE HTML> <html> <head&

  • jQuery+JSON+jPlayer实现QQ空间音乐查询功能示例

    演示地址: http://bejson.com/demos/qqmusic/ 代码下载:http://www.jqdemo.com/932.html 查询QQ音乐是很早前就出来的一个接口. 这里使用jQuery和jPlayer来实现QQ空间音乐的查询. 首先感谢bejson收集的各种有用的接口,当然也包含QQ空间音乐接口. 它的网址是:http://www.bejson.com/webInterface.php 我们要使用的接口位于bejson接口页面中的音乐接口栏里. QQ音乐接口地址: ht

  • 查询json的数据结构的8种方式简介

    查询json的数据结构的8种方式: JsonSQL JsonSQL实现了使用SQL select语句在json数据结构中查询的功能.主页:http://www.trentrichardson.com/jsonsql/ 例子: 复制代码 代码如下: jsonsql.query("select * from json.channel.items order by title desc",json); JSONPath JSONPath就像是针对JSON数据结构的XPath.主页:http:

  • 详解JSON1:使用TSQL查询数据和更新JSON数据

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

  • 详解mysql慢日志查询

    慢日志查询作用 慢日志查询的主要功能就是,记录sql语句中超过设定的时间阈值的查询语句.例如,一条查询sql语句,我们设置的阈值为1s,当这条查询语句的执行时间超过了1s,则将被写入到慢查询配置的日志中. 慢查询主要是为了我们做sql语句的优化功能. 慢日志查询配置项说明 打开mysql,通过以下命令查看相关配置: mysql> show variables like '%query%'; +------------------------------+---------------------

  • 详解MySQL的sql_mode查询与设置

    1,执行SQL查看 select @@session.sql_mode; 全局级别: 查看 select @@global.sql_mode; 2,修改 set @@session.sql_mode='xx_mode' set session sql_mode='xx_mode' 全局级别:修改 set global sql_mode='xx_mode'; set @@global.sql_mode='xx_mode'; session均可省略,默认session,仅对当前会话有效 全局修改的话

  • 详解MongoDB的条件查询和排序

    目录 find 方法 条件组合 比较操作符 IN 查询 限制返回结果条数和跳过数据 排序 总结 find 方法 查询的基本格式是db.collection.find({条件}),其中条件是可选的,类似于 MySQL 的 WHERE 条件一样.示例如下: // 查找全部文档 db.collection.find({}); // 查找指定条件的文档 db.collection.find({key: value}); // 查找昵称为岛上码农的用户 db.users.find({nickname: '

  • 详解mysql跨库查询解决方案

    1.第一种跨库查询,是在同一个mysql服务器下两个不同的数据库之间的联查,关系如下图 在同一个mysql服务器下,不同的两个数据直接加上库名就可以实现跨库查询了 select * from t_test1 t1, test2.t_test2 t2 where t1.id = t2.id 执行sql查询到一下结果 2.第二种跨库查询,是在两台不同服务器(物理服务器)上分别安装的mysql服务器,实现跨库查询,其实现原理类似一个虚拟映射,需要用到mysql的另一个存储引擎Federated,FED

  • 详解Redis的慢查询日志

    Redis慢查询日志帮助开发和运维人员定位系统存在的慢操作.慢查询日志就是系统在命令执行前后计算每条命令的执行时间,当超过预设阀值,就将这条命令的相关信息(慢查询ID,发生时间戳,耗时,命令的详细信息)记录下来. Redis客户端一条命令分为如下四部分执行: 需要注意的是,慢查询日志只是统计步骤3)执行命令的时间,所以慢查询并不代表客户端没有超时问题.需要注意的是,慢查询日志只是统计步骤3)执行命令的时间,所以慢查询并不代表客户端没有超时问题. 一.慢查询的配置参数: 慢查询的预设阀值 slow

  • 详解MySQL like如何查询包含'%'的字段(ESCAPE用法)

    在SQl like语句中,比如 SELECT * FROM user WHERE username LIKE '%luchi%' SELECT * FROM user WHERE username LIKE '_luchi_', % 作为通配符通配多个 _ 作为通配符通配一个 但当like 所要查询的字段中含有 % ,我们如何去查: 这个时候就需要指明 字段中的那个'%' 不作为通配符: 这里就需要用到 ESCAPE 转义 测试: 这里我们使用这张表 转义前: SELECT * FROM use

  • 详解.NET Core 3.0 里新的JSON API

    为什么需要新的 JSON API ? JSON.NET  大家都用过,老版本的 ASP.NET Core 也依赖于 JSON.NET . 然而这个依赖就会引起一些版本问题:例如 ASP .NET  Core某个版本需要使用 JSON .NET  v10 ,而另一个库需要使用 JSON.NET  v11 :或者 JSON .NET   出现了一个新版本,而ASP .NET Core 还不能支持这个版本,而您却想使用该版本. System.Text.Json   随着 NET Core  3.0 的

  • 详解Vue 数据更新了但页面没有更新的 7 种情况汇总及延伸总结

    如果你发现你自己需要在 Vue 中做一次强制更新,99.9% 的情况,是你在某个地方做错了事. 1. Vue 无法检测实例被创建时不存在于 data 中的 property 原因:由于 Vue 会在初始化实例时对 property 执行 getter/setter 转化,所以 property 必须在 data 对象上存在才能让 Vue 将它转换为响应式的. 场景: var vm = new Vue({ data:{}, // 页面不会变化 template: '<div>{{message}

  • thinkPHP5实现的查询数据库并返回json数据实例

    本文实例讲述了thinkPHP5实现的查询数据库并返回json数据.分享给大家供大家参考,具体如下: TP5 实现查询数据库返回json数据(返回json数据函数实例) 返回结果: 复制代码 代码如下: {"code":0,"msg":"\u6570\u636e\u8fd4\u56de\u6210\u529f","count":1000,"data":[{"id":617,"t

随机推荐