目录
解析不规则json
select
id,uid,barcode,czrq,specimen,registerdata,billtime,billdata,jzsj,jzdata,state
from [wzh].[dbo].[m_web_yghs_hsjl] a
where uid='50301' and state=0
其中registerdata 字段就是我们想要解析的json数据Registerdata 字段的内容 如下：
{"Root":{"Space":"","Local":""},"Data":{"XMLName":{"Space":"","Local":"Data"},"Ie":{"XMLNames":{"Space":"","Local":""},"GH_ID":"760238","BR_ID":"579009","BR_MZH":"WM00002333","BR_XM":"张小三","KS_ID":"7","KS":"健康管理科"}}}
其中呢，又以这段为主要提取数据BR_ID":"579009","BR_MZH":"WM00002333","BR_XM":"张小三","KS_ID":"7","KS":"健康管理科"
感觉这个字段 不像是正规的json 格式的数据，找了好多种办法也不能正常解析。
退而求其次，只能把想要的数据截取出来，然后拼凑成json格式，再解析。
我的做法是：
1、 用REVERSE()函数 将Registerdata字段 倒过来 变成这样
select
REVERSE(cast (registerdata as NVARCHAR(MAX)))
from [wzh].[dbo].[m_web_yghs_hsjl] a
where uid='50301' and state=0
}}}"科理管康健":"SK","7":"DI_SK","帅晓张
":"MX_RB","33320000MW":"HZM_RB","900975":"DI_RB","832067":"DI_HG",}"":"lacoL","":"ecapS"{:"semaNLMX"{:"eI",}"ataD":"lacoL","":"ecapS"{:"emaNLMX"{:"ataD",}"":"lacoL","":"ecapS"{:"tooR"{
2、 再根据逗号，大括号“,}” 获取到主要数据的后位置，主要数据即如下这段
}}}"科理管康健":"SK","7":"DI_SK","帅晓张
":"MX_RB","33320000MW":"HZM_RB","900975":"DI_RB","832067":"DI_HG",}
select
charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX)))) 主要数据截取长度
from [wzh].[dbo].[m_web_yghs_hsjl] a
where uid='50301' and state=0
3、 接下来用left() 函数截取所需数据，即会用到以上两组SQL的字段
select
left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX)))))
from [wzh].[dbo].[m_web_yghs_hsjl] a
where uid='50301' and state=0
结果如下：
为了将后边的逗号‘，’去掉，需要再left() 函数，后边的参数再减掉一个1
select
left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX))))-1)
from [wzh].[dbo].[m_web_yghs_hsjl] a
where uid='50301' and state=0
4、 然后在用REVERSE()函数,将字段内容调转过来
select
REVERSE(left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX))))-1))
from [wzh].[dbo].[m_web_yghs_hsjl] a
where uid='50301' and state=0
5、接下来用 替换函数 stuff() 将三个}}} 替换为 一个}
观察上边的数据发现，在字段的左边加上“{”，右边去掉两个‘}’ 这个json 就拼接完成了
select
stuff( REVERSE(left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX))))-1)),
charindex('}}}',REVERSE(left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX))))-1))) ,
3,'}')
from [wzh].[dbo].[m_web_yghs_hsjl] a
where uid='50301' and state=0
6、 左边拼接“{”，就很简单了 直接+ 走起
select
'{'+stuff (REVERSE(left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX))))-1)),
charindex('}}}',REVERSE(left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX))))-1))) ,3,'}')
from [wzh].[dbo].[m_web_yghs_hsjl] a
where uid='50301' and state=0
看起来就很完美，哈哈哈。
7、接下来就是使用JSON_VALUE()函数解析这段拼凑的json 数据了
一大波SQL 来袭
select
JSON_VALUE(cast ('{'+stuff (REVERSE(left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX))))-1)),
charindex('}}}',REVERSE(left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX))))-1))) ,
3,'}') as NVARCHAR(MAX)),'$.GH_ID') AS 挂号ID
,JSON_VALUE(cast ('{'+stuff (REVERSE(left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX))))-1)),
charindex('}}}',REVERSE(left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX))))-1))) ,
3,'}') as NVARCHAR(MAX)),'$.BR_ID') AS 病人ID
,JSON_VALUE(cast ('{'+stuff (REVERSE(left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX))))-1)),
charindex('}}}',REVERSE(left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX))))-1))) ,
3,'}') as NVARCHAR(MAX)),'$.BR_MZH') AS 病人门诊号
,JSON_VALUE(cast ('{'+stuff (REVERSE(left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX))))-1)),
charindex('}}}',REVERSE(left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX))))-1))) ,
3,'}') as NVARCHAR(MAX)),'$.BR_XM') AS 病人姓名
,JSON_VALUE(cast ('{'+stuff (REVERSE(left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX))))-1)),
charindex('}}}',REVERSE(left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX))))-1))) ,
3,'}') as NVARCHAR(MAX)),'$.KS_ID') AS 挂号科室ID
,JSON_VALUE(cast ('{'+stuff (REVERSE(left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX))))-1)),
charindex('}}}',REVERSE(left (REVERSE(cast (registerdata as NVARCHAR(MAX))), charindex(',}',REVERSE(cast (registerdata as NVARCHAR(MAX))))-1))) ,
3,'}') as NVARCHAR(MAX)),'$.KS') AS 挂号科室名称
from [wzh].[dbo].[m_web_yghs_hsjl] a
where state= and uid='50301'
