SQLServer中OPENJSON函数的用法

OPENJSON 要求兼容性级别 130，即SQLSERVER2016以上版本

1、默认输出的 OPENJSON

在不提供结果的显式架构的情况下使用 OPENJSON 函数时（在 OPENJSON 之后不使用 WITH 子句），该函数将返回包含以下三列的表：

1. 输入对象中属性的名称（或输入数组中元素的索引）。
2. 属性或数组元素的值。
3. 类型（例如，字符串、数字、布尔值、数组或对象）。

OPENJSON 以单独的行返回 JSON 对象的每个属性或数组的每个元素。

示例1：JSON 对象的每个属性返回一行

登录后复制

DECLARE @json NVARCHAR(MAX)
 
SET @json='{
    "姓名": "张三", 
    "年龄": 25, 
    "地址": {
        "省": "浙江", 
        "市": "杭州"
    }, 
    "特长": [
        "删库", 
        "甩锅", 
        "跑路"
    ]
}';
 
SELECT * FROM OPENJSON(@json);

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.

示例2：数组的每个元素返回一行

登录后复制

DECLARE @json NVARCHAR(MAX)
 
SET @json='[
    "跑路", 
    24, 
    {
        "姓名": "张三"
    }, 
    [
        1, 
        2
    ]
]';
 
SELECT * FROM OPENJSON(@json)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.

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) '$')

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.

示例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属性进行遍历。
 --这样就可以正确地获取每个人员的姓名、年龄以及他们的技能特长，避免了列名无效的错误。

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.

示例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两个列来分别提取这些值。

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.
• 33.
• 34.
• 35.
• 36.
• 37.
• 38.
• 39.
• 40.
• 41.
• 42.
• 43.
• 44.
• 45.
• 46.

从表的字段中中解析json

登录后复制

SELECT 
    test.omsid,
	json.[key],
	json.value,
	json.type
FROM 
    test
CROSS APPLY 
    OPENJSON(data) AS json
where 1=1

--test是表名,data是test表中的存储了json字符串的字段

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.

原创作者: 190242856 转载于: https://blog.51cto.com/190242856/11285821