【SQL Server】解析json
有个需求，对一个json进行解析，如果通过字符串的拆分的话，还挺麻烦（特别是SQL Server也没那么好用），数据库版本是SQL Server 2016，有个新的功能openjson
jeson串样例：
|
1 |
["ftp://FtpAdmin:Oracle123@192.168.31.100:21/Resource/xxf/2019/07/31/840128cba6684838a8f4b3ee2a71fb5f[测试1].jpg","ftp://FtpAdmin:Oracle123@192.168.31.100:21/Resource/xxf/2019/07/31/f22e0f58395c486cb0f4e587149f9ca7[测试2].jpg","ftp://FtpAdmin:Oracle123@192.168.31.100:21/Resource/xxf/2019/07/31/37c2a8c2092a48068ec015e4e13dd212[测试3].jpg"] |
SQL Server 2016 通过openjeson解析：
|
1 2 3 |
DECLARE @json NVARCHAR(MAX) SET @json='["ftp://FtpAdmin:Oracle123@192.168.31.100:21/Resource/xxf/2019/07/31/840128cba6684838a8f4b3ee2a71fb5f[测试1].jpg","ftp://FtpAdmin:Oracle123@192.168.31.100:21/Resource/xxf/2019/07/31/f22e0f58395c486cb0f4e587149f9ca7[测试2].jpg","ftp://FtpAdmin:Oracle123@192.168.31.100:21/Resource/xxf/2019/07/31/37c2a8c2092a48068ec015e4e13dd212[测试3].jpg"]' SELECT * FROM OPENJSON(@json) |
实际情况是要对字符串进行拆分，如下：
|
1 2 3 4 5 6 7 8 |
DECLARE @json NVARCHAR(MAX) SET @json='["ftp://FtpAdmin:Oracle123@192.168.31.100:21/Resource/xxf/2019/07/31/840128cba6684838a8f4b3ee2a71fb5f[测试1].jpg","ftp://FtpAdmin:Oracle123@192.168.31.100:21/Resource/xxf/2019/07/31/f22e0f58395c486cb0f4e587149f9ca7[测试2].jpg","ftp://FtpAdmin:Oracle123@192.168.31.100:21/Resource/xxf/2019/07/31/37c2a8c2092a48068ec015e4e13dd212[测试3].jpg"]' SELECT [key]+1 as seq, value as dpath, replace(replace(right(value,CHARINDEX('[',reverse(value),1)),'[',''),']','') as fname, CONVERT(varchar,GETDATE(),120) as dt FROM OPENJSON(@json) |
这里要注意一下，key是关键字，所以，指定查询key时，需要加上[]