SQL Server中JSON函数的用法详解

本文将为大家详细讲解SQL Server 中JSON函数的用法，包括以下部分：
1. JSON数据类型介绍
2. JSON的查询、修改、删除和插入
3. SQL Server中JSON函数的详细说明
4. 函数示例说明

1. JSON数据类型介绍

在SQL Server2016中首次引入了JSON数据类型，使我们可以在SQL Server中直接处理JSON格式的数据，而无需对JSON格式数据进行分割和解析处理。
JSON(JavaScript Object Notation)是一种轻量级的数据交换格式，常用于各种WEB应用中，在不同的编程语言之间的数据传输、传递、存储都很方便。

2. JSON的查询、修改、删除和插入

JSON格式数据支持查询、修改、删除、插入等操作。对于JSON格式数据，先通过OPENJSON将数据解析成关系表格形式进行操作。

2.1 查询JSON数据

查询JSON数据可以通过SELECT语句进行，但是需要注意在查询的时候需要使用JSON_VALUE函数或者JSON_QUERY函数进行解析，在使用函数的过程中还需指定路径。例如：

SELECT
  JSON_VALUE(json_data,'$.name') AS name,
  JSON_VALUE(json_data,'$.address') AS address,
  JSON_VALUE(json_data,'$.age') AS age
FROM
  person

在json_data中解析$.name、$.address、$.age并显示出来。

2.2 修改JSON数据

修改JSON数据使用JSON_MODIFY函数，使用时需要指定要修改的JSON路径和要修改的值。例如：

UPDATE
  person
SET
  json_data=JSON_MODIFY(json_data,'$.name','John')
WHERE 
  JSON_VALUE(json_data,'$.name')='Tom'

在person表中查找姓名为Tom的记录，并将名字修改为John。

2.3 删除JSON数据

删除JSON数据同样使用JSON_MODIFY函数，只需要将要修改的值设为NULL即可。例如：

UPDATE
  person
SET
  json_data=JSON_MODIFY(json_data,'$.name',NULL)
WHERE
  JSON_VALUE(json_data,'$.name')='Tom'

在person表中查找姓名为Tom的记录，并将姓名属性值删除。

2.4 插入JSON数据

插入JSON数据可以使用JSON_MODIFY函数，在实现插入属性的同时也可以实现更新属性。例如：

UPDATE
  person
SET
  json_data=JSON_MODIFY(json_data,'$.newfield','newvalue')
WHERE
  JSON_VALUE(json_data,'$.name')='Tom'

在person表中查找姓名为Tom的记录并添加一个名为newfield的属性并且属性值为newvalue(如果该属性的设有值则会更新属性值)。

3. SQL Server中JSON函数的详细说明

SQL Server中JSON函数包括OPENJSON、JSON_VALUE、JSON_QUERY、ISJSON、JSON_MODIFY等函数。

3.1 OPENJSON函数

OPENJSON函数允许我们读取JSON数组或对象，返回一个带有key和value两个属性的表格形式。例如：

DECLARE @json NVARCHAR(MAX)
SET @json=
'[{
    "id": 1,
    "name": "Tom",
    "age": 23,
    "address": "Shanghai"
}]'

SELECT *
FROM OPENJSON(@json)

3.2 JSON_VALUE函数

JSON_VALUE函数用于返回JSON格式的单个属性值。例如：

DECLARE @json NVARCHAR(MAX)
SET @json=
 '{
    "id": 1,
    "name": "Tom",
    "age": 23,
    "address": "Shanghai"
  }'

SELECT JSON_VALUE(@json,'$.id')

3.3 JSON_QUERY函数

JSON_QUERY函数用于返回JSON格式数据的属性集合。例如：

DECLARE @json NVARCHAR(MAX)
SET @json=
 '[{
    "id": 1,
    "name": "Tom",
    "age": 23,
    "address": "Shanghai"
  },{
    "id": 2,
    "name": "Jerry",
    "age": 32,
    "address": "Beijing"
  }]'

SELECT JSON_QUERY(@json,'$[*].name')

3.4 ISJSON函数

ISJSON函数用于检查JSON格式的字符串是否合法。例如：

SELECT ISJSON('{"name":"Tom","age":23,"address":"Shanghai"}')

3.5 JSON_MODIFY函数

JSON_MODIFY函数用于修改JSON格式数据。例如：

DECLARE @json NVARCHAR(MAX)
SET @json=
'{
    "id": 1,
    "name": "Tom",
    "age": 23,
    "address": "Shanghai"
}'

SET @json=JSON_MODIFY(@json,'$.name','John')
SELECT @json

4. 函数示例说明

以下是两个JSON函数的实际使用案例。

4.1 实现JSON数据的插入、更新和删除

--插入一条JSON数据
INSERT INTO person(json_data)
VALUES('{
  "name": "Tom",
  "address": "Shanghai",
  "age": 23
 }')

--更新一条JSON数据
UPDATE
  person
SET
  json_data=JSON_MODIFY(json_data,'$.name','John')
WHERE 
  JSON_VALUE(json_data,'$.name')='Tom'

--删除一条JSON数据
UPDATE
  person
SET
  json_data=JSON_MODIFY(json_data,'$.name',NULL)
WHERE
  JSON_VALUE(json_data,'$.name')='John'

4.2 实现JSON格式数据的查询

  SELECT
    JSON_VALUE(json_data,'$.name') AS name,
    JSON_VALUE(json_data,'$.address') AS address,
    JSON_VALUE(json_data,'$.age') AS age
  FROM
    person

以上就是SQL Server中JSON函数的详细说明，希望对大家的学习有所帮助。