首页 > 数据库 >使用内置函数 (SQL Server) 验证、查询和更改 JSON 数据

使用内置函数 (SQL Server) 验证、查询和更改 JSON 数据

时间：2024-04-12 18:33:44浏览次数：28  
标签：示例 VALUE Server JSON SQL QUERY givenName

使用内置函数 (SQL Server) 验证、查询和更改 JSON 数据

  • 项目
  • 2023/09/03
 

本文内容

  1. 此页上的示例 JSON 文本
  2. 使用 ISJSON 函数验证 JSON 文本
  3. 使用 JSON_VALUE 函数从 JSON 文本中提取值
  4. 使用 JSON_QUERY 函数从 JSON 文本中提取对象或数组

适用于： SQL Server 2016 (13.x) 及更高版本 Azure SQL 数据库Azure SQL 托管实例

JSON 的内置支持包括本主题简要介绍的下列内置函数。

  • ISJSON 测试字符串是否包含有效 JSON。

  • JSON_VALUE 从 JSON 字符串中提取标量值。

  • JSON_QUERY 从 JSON 字符串中提取对象或数组。

  • JSON_MODIFY 更新 JSON 字符串中属性的值，并返回已更新的 JSON 字符串。

此页上的示例 JSON 文本

此页上的示例使用与以下示例中所示内容类似的 JSON 文本：

JSON 
{
  "id": "WakefieldFamily",
  "parents": [
      { "familyName": "Wakefield", "givenName": "Robin" },
      { "familyName": "Miller", "givenName": "Ben" }
  ],
  "children": [
      {
        "familyName": "Merriam",
        "givenName": "Jesse",
        "gender": "female",
        "grade": 1,
        "pets": [
            { "givenName": "Goofy" },
            { "givenName": "Shadow" }
        ]
      },
      { 
        "familyName": "Miller",
         "givenName": "Lisa",
         "gender": "female",
         "grade": 8 }
  ],
  "address": { "state": "NY", "county": "Manhattan", "city": "NY" },
  "creationDate": 1431620462,
  "isRegistered": false
}

此 JSON 文档包含嵌套的复杂元素，存储在下面的示例表中：

SQL 
CREATE TABLE Families (
   id int identity constraint PK_JSON_ID primary key,
   doc nvarchar(max)
)

使用 ISJSON 函数验证 JSON 文本

ISJSON 函数测试字符串是否包含有效 JSON。

下面的示例将返回 JSON 列包含有效 JSON 文本的行。 请注意，如果没有显式 JSON 约束，则可在 NVARCHAR 列中输入任意文本：

SQL 
SELECT *
FROM Families
WHERE ISJSON(doc) > 0

有关详细信息，请参阅 ISJSON (Transact-SQL)。

使用 JSON_VALUE 函数从 JSON 文本中提取值

JSON_VALUE 函数从 JSON 字符串中提取标量值。 下面的查询将返回其中 id JSON 字段与值 AndersenFamily 一致的文档，按 city 和 state JSON 字段排序：

SQL 
SELECT JSON_VALUE(f.doc, '$.id')  AS Name, 
       JSON_VALUE(f.doc, '$.address.city') AS City,
       JSON_VALUE(f.doc, '$.address.county') AS County
FROM Families f 
WHERE JSON_VALUE(f.doc, '$.id') = N'AndersenFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC, JSON_VALUE(f.doc, '$.address.state') ASC

此查询结果显示在下表中：

名称城市县
AndersenFamily NY 曼哈顿

有关详细信息，请参阅 JSON_VALUE (Transact-SQL)。

使用 JSON_QUERY 函数从 JSON 文本中提取对象或数组

JSON_QUERY 函数从 JSON 字符串中提取对象或数组。 下面的示例演示了如何在查询结果中返回 JSON 片段。

SQL 
SELECT JSON_QUERY(f.doc, '$.address') AS Address,
       JSON_QUERY(f.doc, '$.parents') AS Parents,
       JSON_QUERY(f.doc, '$.parents[0]') AS Parent0
FROM Families f 
WHERE JSON_VALUE(f.doc, '$.id') = N'AndersenFamily'

此查询结果显示在下表中：

地址父项Parent0
{ "state": "NY", "county": "Manhattan", "city": "NY" } [{ "familyName": "Wakefield", "givenName": "Robin" }, {"familyName": "Miller", "givenName": "Ben" } ] { "familyName": "Wakefield", "givenName": "Robin" }

有关详细信息，请参阅 JSON_QUERY (Transact-SQL)。

分析嵌套式 JSON 集合

通过 OPENJSON 函数，可将 JSON 子数组转换为行集，然后将其与父元素联接在一起。 例如，可返回所有家庭文档，并将其与存储为内部 JSON 数组的 children 对象“联接”起来：

SQL 
SELECT JSON_VALUE(f.doc, '$.id')  AS Name, 
       JSON_VALUE(f.doc, '$.address.city') AS City,
       c.givenName, c.grade
FROM Families f
		CROSS APPLY OPENJSON(f.doc, '$.children')
			WITH(grade int, givenName nvarchar(100))  c

此查询结果显示在下表中：

名称城市givenName年级
AndersenFamily NY Jesse 1
AndersenFamily NY Lisa 8

最终我们得到两个行，因为一个父行与通过分析子级子数组的两个元素产生的两个子行联接在一起。 OPENJSON 函数分析 doc 列中的 children 片段，并返回每个元素中的 grade 和 givenName 作为一个行集。 此行集可以与父文档进行联接。

查询嵌套式分层 JSON 子数组

可应用多个 CROSS APPLY OPENJSON 调用以查询嵌套式 JSON 结构。 本示例中使用的 JSON 文档具有名为 children 的嵌套数组，其中每个子级都有 pets 的嵌套数组。 下面的查询将分析每个文档中的子级，将每个数组对象作为行返回，然后分析 pets 数组：

SQL 
SELECT	familyName,
	c.givenName AS childGivenName,
	c.firstName AS childFirstName,
	p.givenName AS petName 
FROM Families f 
	CROSS APPLY OPENJSON(f.doc) 
		WITH (familyName nvarchar(100), children nvarchar(max) AS JSON)
		CROSS APPLY OPENJSON(children) 
		WITH (givenName nvarchar(100), firstName nvarchar(100), pets nvarchar(max) AS JSON) as c
			OUTER APPLY OPENJSON (pets)
			WITH (givenName nvarchar(100))  as p

首次进行 OPENJSON 调用将使用 AS JSON 子句返回 children 数组的片段。 此数组片段将提供给第二个 OPENJSON 函数，该函数将返回每个小孩的 firstName 和 givenName 以及pets 数组。 pets 的数组将提供给第三个 OPENJSON 函数，该函数将返回宠物的 givenName。 此查询结果显示在下表中：

familyNamechildGivenNamechildFirstNamepetName
AndersenFamily Jesse Merriam Goofy
AndersenFamily Jesse Merriam Shadow
AndersenFamily Lisa Miller NULL

根文档与两个 children 行进行了联接，这两个行由生成两个行（或元组）的首次 OPENJSON(children) 调用返回。 然后，使用 OUTER APPLY 运算符将每行与由 OPENJSON(pets) 生成的新行进行联接。 Jesse 有两只宠物，因此 (AndersenFamily, Jesse, Merriam) 与为 Goofy 和 Shadow 生成的两行进行了联接。 Lisa 没有宠物，因此 OPENJSON(pets) 没有为该元组返回的任何行。 但是，由于我们使用的是 OUTER APPLY，因此这列的结果是 NULL。 如果我们使用 CROSS APPLY 而不是 OUTER APPLY，则不会在结果中返回 Lisa，因为没有可以与该元组联接的任何宠物行。

对比 JSON_VALUE 与 JSON_QUERY

JSON_VALUE 和 JSON_QUERY 之间的主要区别在于 JSON_VALUE 返回标量值，而 JSON_QUERY 返回数组或对象。

请参考以下示例 JSON 文本。

JSON 
{
	"a": "[1,2]",
	"b": [1, 2],
	"c": "hi"
}

在此示例 JSON 文本中，数据成员“a”和“c”是字符串值，而数据成员“b”是数组。 JSON_VALUE 和 JSON_QUERY 返回以下结果：

路径JSON_VALUE 返回JSON_QUERY 返回
$ NULL 或错误 { "a": "[1,2]", "b": [1,2], "c":"hi"}
$.a [1,2] NULL 或错误
$.b NULL 或错误 [1,2]
$.b[0] 1 NULL 或错误
$.c 你好 NULL 或错误

使用 AdventureWorks 示例数据库测试 JSON_VALUE 和 JSON_QUERY

通过使用 AdventureWorks 示例数据库运行以下示例，对本主题中所述的内置函数进行测试。 有关何处获取 AdventureWorks 的信息，以及如何通过运行脚本添加用于测试的 JSON 数据，请参阅测试驱动的内置 JSON 支持。

在下面的示例中，SalesOrder_json 表中的 Info 列包含了 JSON 文本。

示例 1 - 返回标准列和 JSON 数据

下面的查询返回将返回标准关系列以及 JSON 列的值。

SQL 
SELECT SalesOrderNumber, OrderDate, Status, ShipDate, Status, AccountNumber, TotalDue,
 JSON_QUERY(Info,'$.ShippingInfo') ShippingInfo,
 JSON_QUERY(Info,'$.BillingInfo') BillingInfo,
 JSON_VALUE(Info,'$.SalesPerson.Name') SalesPerson,
 JSON_VALUE(Info,'$.ShippingInfo.City') City,
 JSON_VALUE(Info,'$.Customer.Name') Customer,
 JSON_QUERY(OrderItems,'$') OrderItems
FROM Sales.SalesOrder_json
WHERE ISJSON(Info) > 0

示例 2 - 聚合和筛选 JSON 值

下面的查询将按客户名称（存储在 JSON 中）和状态（存储在普通列中）来汇总小计。 然后将按市/县（存储在 JSON 中）和 OrderDate（存储在普通列中）来筛选结果。

SQL 
DECLARE @territoryid INT;
DECLARE @city NVARCHAR(32);

SET @territoryid=3;

SET @city=N'Seattle';

SELECT JSON_VALUE(Info, '$.Customer.Name') AS Customer, Status, SUM(SubTotal) AS Total
FROM Sales.SalesOrder_json
WHERE TerritoryID=@territoryid
 AND JSON_VALUE(Info, '$.ShippingInfo.City') = @city
 AND OrderDate > '1/1/2015'
GROUP BY JSON_VALUE(Info, '$.Customer.Name'), Status
HAVING SUM(SubTotal)>1000

使用 JSON_MODIFY 函数更新 JSON 文本中的属性值

JSON_MODIFY 函数更新 JSON 字符串中属性的值，并返回已更新的 JSON 字符串。

以下示例将更新包含 JSON 的变量中的 JSON 属性的值。

SQL 
SET @info = JSON_MODIFY(@jsonInfo, "$.info.address[0].town", 'London')

有关详细信息，请参阅 JSON_MODIFY (Transact-SQL)。

详细了解 SQL Server 和 Azure SQL 数据库中的 JSON

Microsoft 视频

 备注

此部分中的某些视频链接在此时可能不起作用。 Microsoft 会将以前在第 9 频道上的内容迁移到新平台。 随着视频迁移到新平台，我们将更新链接。

另请参阅

ISJSON (Transact-SQL)
JSON_VALUE (Transact-SQL)
JSON_QUERY (Transact-SQL)
JSON_MODIFY (Transact-SQL)
JSON 路径表达式 (SQL Server)

   

标签：示例,VALUE,Server,JSON,SQL,QUERY,givenName
From： https://www.cnblogs.com/sexintercourse/p/18131898

相关文章

  • mysql-子查询的学习
    子查询由一个具体的需求，引入子查询谁的工资比Abel的高SELECT*fromemployeesWHEREsalary>(SELECTsalaryFROMemployeesWHERElast_name='Abel')--自连接SELECTe2.*......
  • MySQL数据库无法远程连接的解决办法
    MySQL数据库无法远程连接的解决办法 远程登陆数据库的时候出现了下面出错信息:ERROR2003(HY000):Can'tconnecttoMySQLserveron'xxx.xxx.xxx.xxx',经过今天下午的反复纠结,关于MySql数据库无法远程连接的问题可以归结为以下几点:1).没有授予相应的......
  • PSQL_标准API和Interface基本的用法和比较
      一、简介   1.API调用的6个参数    2.处理后错误信息的处理    3.API成功与否的判断依据    4.API和Interface的区别和优点 二. 具体分析1、API调用的6个参数        2. 处理后错误信息的处理IF(fnd_msg_pub.count_msg>0)THE......
  • mysql 索引设计原则
    适合添加索引的情况1.字段的数值有唯一性的限制索引本身可以起到约束的作用，比如唯一索引、主键索引都可以起到唯一性约束的，因此我们在创建数据表时，如果某个字段时唯一的，就可以直接创建唯一性索引或主键索引。不要以为唯一索引影响了insert的速度，这个速度损耗可以忽略不计，单体......
  • 使用 SQL SERVER PROFILER 监测死锁
    作为DBA，可能经常会遇到有同事或者客户反映经常发生死锁，影响了系统的使用。此时，你需要尽快侦测和处理这类问题。死锁是当两个或者以上的事务互相阻塞引起的。在这种情况下两个事务会无限期地等待对方释放资源以便操作。下面是死锁的示意图： 本文将使用SQLServerProfile......
  • MySQL基础
    1，初识SQL语句SQL语句：操作文件夹（库） 增 createdatabasedb1charsetutf8; 查 showcreatedatabasedb1; showdatabases; 改 alterdatabasedb1charsetgbk; 删 dropdatabasedb1;操作文件（表） 切换文件夹：usedb1; 查看当前所在文件夹：selectdatabase(); ......
  • JSON.parse() 方法中里面有函数，转化完成后是字符串问题
    要将JSON字符串中的函数还原回原来的函数对象，你可以使用JSON.parse()方法，并提供一个reviver函数来将字符串表示的函数重新转换为函数对象。以下是你可以使用的代码示例：varobj={"name":"Runoob","alexa":function(){return10000;},"site":"www.runoob.com"};//......
  • MyBatis动态SQL
    MyBatis动态SQL动态SQL简介动态SQL是MyBatis的强大特性之一。如果你使用过JDBC或其它类似的框架，你应该能理解根据不同条件拼接SQL语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态SQL，可以彻底摆脱这种痛苦。使用动态......
  • mysql修改密码报错:Your password does not satisfy the current policy requirements
    参考https://blog.csdn.net/u013449046/article/details/106455041这是mysql初始化时，使用临时密码，修改自定义密码时，由于自定义密码比较简单，就出现了不符合密码策略的问题。密码策略问题异常信息：ERROR1819(HY000):Yourpassworddoesnotsatisfythecurrentpolicyrequ......
  • MySQL的CDC数据实时同步
    MySQL的CDC数据实时同步 背景近段时间，业务系统架构基本完备，数据层面的建设比较薄弱，因为笔者目前工作重心在于搭建一个小型的数据平台。优先级比较高的一个任务就是需要近实时同步业务系统的数据（包括保存、更新或者软删除）到一个另一个数据源，持久化之前需要清洗数据并且构建一......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99