OPENJSON 要求兼容性级别 130，即SQLSERVER2016以上版本
1、默认输出的 OPENJSON
在不提供结果的显式架构的情况下使用 OPENJSON 函数时（在 OPENJSON 之后不使用 WITH 子句），该函数将返回包含以下三列的表：
- 输入对象中属性的名称（或输入数组中元素的索引）。
- 属性或数组元素的值。
- 类型（例如，字符串、数字、布尔值、数组或对象）。
OPENJSON 以单独的行返回 JSON 对象的每个属性或数组的每个元素。
示例1：JSON 对象的每个属性返回一行
DECLARE @json NVARCHAR(MAX)
SET @json='{
"姓名": "张三",
"年龄": 25,
"地址": {
"省": "浙江",
"市": "杭州"
},
"特长": [
"删库",
"甩锅",
"跑路"
]
}';
SELECT * FROM OPENJSON(@json);
示例2：数组的每个元素返回一行
DECLARE @json NVARCHAR(MAX)
SET @json='[
"跑路",
24,
{
"姓名": "张三"
},
[
1,
2
]
]';
SELECT * FROM OPENJSON(@json)
2、显式结构的 OPENJSON
在 OPENJSON 函数的 WITH 子句指定结果的架构，该函数返回的表只包含 WITH 子句中定义的列。
在可选的 WITH 子句中，指定输出列、列类型和每个输出值的 JSON 源属性的路径。
OPENJSON 循环访问 JSON 对象的数组，读取每一列指定路径上的值，并将值转换为指定类型。
示例1：从json中解析数据为二维表
DECLARE @json NVARCHAR(MAX)
SET @json='{
"name":"张三",
"age":25,
"skills": [
"删库",
"甩锅",
"跑路"
]
}';
-- 其中 $ 表示根路径
SELECT
JSON_VALUE(@json, '$.name') as name,
JSON_VALUE(@json, '$.age') as age,
skills
FROM
OPENJSON(@json, '$.skills')
WITH(skills varchar(20) '$')
示例2：从数组中解析数据为二维表
DECLARE @json NVARCHAR(MAX) = '[{
"name":"张三",
"age":25,
"skills": [
"删库",
"甩锅",
"干饭"
]
},
{
"name":"李四",
"age":27,
"skills": [
"画饼",
"跑路"
]
}]';
SELECT
JSON_VALUE(value, '$.name') AS 姓名,
JSON_VALUE(value, '$.age') AS 年龄,
skill.特长
FROM
OPENJSON(@json) AS items -- 遍历JSON数组的每一项
CROSS APPLY OPENJSON(items.value, '$.skills') -- 针对每个项中的skills数组
WITH(
特长 varchar(20) '$'
) AS skill;
--在OPENJSON的外部调用中使用了value关键字来引用每个JSON对象（即人员信息），
--而在内部调用中继续使用items.value来针对特定对象的skills属性进行遍历。
--这样就可以正确地获取每个人员的姓名、年龄以及他们的技能特长，避免了列名无效的错误。
示例3：从数组中解析数据为二维表（带嵌套json对象）
DECLARE @json NVARCHAR(MAX) = '[
{
"name": "张三",
"age": 25,
"address": {
"province": "四川",
"city": "成都"
},
"skills": [
"删库",
"甩锅",
"干饭"
]
},
{
"name": "李四",
"age": 27,
"address": {
"province": "浙江",
"city": "杭州"
},
"skills": [
"画饼",
"跑路"
]
}
]';
SELECT
JSON_VALUE(items.value, '$.name') AS name,
JSON_VALUE(items.value, '$.age') AS age,
addr.province AS province,
addr.city AS city,
skill.skills
FROM
OPENJSON(@json) AS items
CROSS APPLY OPENJSON(items.value, '$.address') WITH(
province varchar(20) '$.province',
city varchar(20) '$.city'
) AS addr
CROSS APPLY OPENJSON(items.value, '$.skills') WITH(
skills varchar(20) '$'
) AS skill;
--在本示例中，添加了一个CROSS APPLY子句来处理address字段中的嵌套JSON对象。
--通过WITH子句定义province和city两个列来分别提取这些值。
解析钉钉OA表单
SELECT
t.表单名,
t.processCode,
t.processInstanceId,
dateadd(hour,-8,t.createTime) as createTime, --对齐时区
dateadd(hour,-8,t.finishTime) as finishTime, --对齐时区
t.result,
result.businessId AS businessId,
result.title AS title,
formComponentValues.name AS name,
formComponentValues.value AS value
FROM
OA表单数据 t
CROSS APPLY OPENJSON(t.data, '$.result') WITH (
businessId VARCHAR(8000) '$.businessId',
title VARCHAR(8000) '$.title'
) AS result
CROSS APPLY OPENJSON(t.data, '$.result.formComponentValues') WITH (
name VARCHAR(8000) '$.name',
value VARCHAR(8000) '$.value'
) AS formComponentValues
where formComponentValues.name is not null示例
--创建表并定义一个nvarchar(max)类型字段的表
CREATE TABLE Products (
ProductId int PRIMARY KEY,
Name nvarchar(50),
Attributes nvarchar(max)
);
--插入两行数据
INSERT INTO Products (ProductId, Name, Attributes) VALUES (1, 'Widget', N'{"Color": "Blue", "Size": "Medium" ,"userid":[{"id":"001","name":"张三"},{"id":"002","name":"李四"},{"id":"003","name":"王五"}]}');
INSERT INTO Products (ProductId, Name, Attributes) VALUES (2, 'Gadget', N'{"Color": "Red", "Size": "Max" ,"userid":[{"id":"101","name":"张三101"},{"id":"102","name":"李四102"},{"id":"103","name":"王五103"}]}');
原始数据表
字段 Attributes 的 json 结构
-- 解析 JSON 字符串并展开为行
SELECT
t.ProductId
,t.name
,t1.Color
,t1.Size
,t2.id
,t2.name
FROM Products t
CROSS APPLY OPENJSON(Attributes)
WITH (Color nvarchar(50) '$.Color',
Size nvarchar(50) '$.Size') as t1
CROSS APPLY OPENJSON(Attributes, '$.userid')
WITH (id VARCHAR(8000) '$.id',
name VARCHAR(8000) '$.name') as t2
解析为标准二维表
