SQL 提取Json串中的值

SQL处理json

前言：工作中遇到了需要使用SQL处理Json串的情景

就去解了一些，并且总结了下来。方便以后使用。

处理 json 一般需要的函数：

1. OPENJSON :OPENJSON 是一种表值函数，可分析 JSON 文本，并以行和列的形式从 JSON 输入返回对象和属性。 具体内容可以查看官网 LINk
2. JSON_VALUE:从 JSON 字符串中提取标量值。 具体内容可以查看官网 LINK
3. JSON_QUERY:从 JSON 字符串中提取对象或数组。 JSON_VALUE 与JSON_QUery的区别可以查看 LINk
4. ISJSON:测试字符串是否包含有效 JSON。

下面是一些常用情况：

一个json串：

Decalre @json nvarchar(max)
Set @json='{
    "name":"张三",
    "age":20,
    "gender":"boy",
    "habbit":[{
        "book":["A","B","C"],
        "game":"LOL",
        "eat":[{
            "food":["vegatable","ice cream"],
			"drink":"[COCO,beer]"
        }]
    }] 
}'

使用SQL语句取下面对应的内容

--* 取habbit的book列表
select * from OPENJSON((Select value from OPENJSON(@json,'$.habbit')),'$.book')

--* 取habbit的book列表中的B项
select JSON_VALUE(value,'$.book[1]') from OPENJSON(@json,'$.habbit')

--* 取habbit中game的值
select JSON_VALUE(value,'$.game') from OPENJSON(@json,'$.habbit')

--* 取habbit中的eat下的food的所有值
select * from OPENJSON((select value from OPENJSON((SELECT value from OPENJSON(@json,'$.habbit')),'$.eat')),'$.food')

--* 取habbit中的eat下的food的ice cream值
select JSON_VALUE(value,'$.food[1]') from OPENJSON((SELECT value from OPENJSON(@json,'$.habbit')),'$.eat')

--* 取habbit中的eat下的drink的所有值
select JSON_VALUE(value,'$.drink') from OPENJSON((SELECT value from OPENJSON(@json,'$.habbit')),'$.eat')

其中我有个需要将得到的所有行根据分号转成一行

就比如我执行

select value from OPENJSON((Select value from OPENJSON(@json,'$.habbit')),'$.book')

会得到 这样的结果

但是我想要ABC 通过 “,” 分割然后在一行

我认为简便的方法：
使用For XML path() 与 STUFF 函数结合

STUFF ( character_expression , start , length ,character_expression )
　　character_expression：字符数据
　　start ：指定删除和插入的开始位置
　　length ：指定要删除的字符数
　　character_expression : 替换start 到 length 的字符数据

这是执行语句

select Stuff((select ',' + value
         FROM OPENJSON((Select value from OPENJSON(@json,'$.habbit')),'$.book')
        For XML PATH('')),1,1,'')

之后的效果是