JSON_VALUE for SQL Server 2012?

Question

我想从SQL中的JSON字符串中提取值。开始编写一个函数来这样做，但后来我想"肯定其他人已经这样做了？当我看到 SQL Server 中现在有一个JSON_VALUE功能时，我非常兴奋......但当我意识到它直到 2016 年才被添加时，我感到非常失望。:(

所以。。。我正在编写我自己的函数版本。我确信它一开始似乎会起作用，然后我偶尔会出错，直到随着时间的推移我完善它。

但我希望有人已经在这方面领先一步，并解决了我在初稿中肯定会忽略的一些问题......希望这里有人可以指出我吗？

Answer 1

好吧，既然似乎还没有人提供任何东西，这就是我到目前为止编写的代码。也许它会帮助下一个站在我的立场上的人。我决定根据我要检索的值的类型使用单独的函数。特别值得注意的是，date 函数用于检索自 1970 年以来的毫秒数的值，十进制函数有一个参数来指定该值是否被引用。

create function [dbo].[GetJsonDateValue](@Key varchar(100), @data nvarchar(max))
returns datetime
as
begin
declare @keyIdx int = charindex(@Key, @data)
declare @valueIdx int = @keyIdx + len(@Key) + 2 -- +2 to account for characters between key and value
declare @termIdx int = charindex(',', @data, @keyIdx)
-- In case it's last item in an object
if @termIdx = 0
set @termIdx = charindex('}', @data, @keyIdx)
declare @valueLength int = @termIdx - @valueIdx
declare @secondsSince1970 bigint = cast(substring(@data, @valueIdx, @valueLength) as bigint) / 1000
declare @retValue datetime = dateadd(s, @secondsSince1970, '19700101')
return @retValue
end
GO
CREATE function [dbo].[GetJsonDecimalValue](@Key varchar(100), @data nvarchar(max), @quoted bit)
returns decimal(9,2)
as
begin
declare @keyIdx int = charindex(@Key, @data)
declare @valueIdx int = @keyIdx + len(@Key) + 2 -- +2 to account for characters between key and value
+ case when @quoted = 1 then 1 else 0 end -- +1 more for quote around value if present
declare @termIdx int = charindex(case @quoted when 1 then '"' else ',' end, @data, @valueIdx)
-- In case it's last item in an object and not quoted
if @quoted = 0 and @termIdx = 0
set @termIdx = charindex('}', @data, @keyIdx)
declare @valueLength int = @termIdx - @valueIdx
if @valueLength = 0
return null
declare @retValue decimal(9,2) = cast(substring(@data, @valueIdx, @valueLength) as decimal(9,2))
return @retValue
end
GO
CREATE function [dbo].[GetJsonStringValue](@Key varchar(100), @data nvarchar(max))
returns varchar(max)
as
begin
declare @keyIdx int = charindex(@Key, @data)
declare @valueIdx int = @keyIdx + len(@Key) + 3 -- +3 to account for characters between key and value
declare @termIdx int = charindex('"', @data, @valueIdx)
declare @valueLength int = @termIdx - @valueIdx
declare @retValue varchar(max) = substring(@data, @valueIdx, @valueLength)
return @retValue
end
GO

Answer 2

首先，谢谢你@BVernon - 你的回答让我走上了正确的轨道。 我对此进行了改进 - 它能够：

• 返回字符串、数字或 null 和句柄
• 处理 JSON 引号转义
• 更好/可靠的发现 - 如果 key 是其他字符串值的一部分，它会按"key":搜索，而不仅仅是按key搜索。

CREATE FUNCTION [dbo].[GetJsonValue](@key varchar(100), @data nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @keyJson varchar(105) = '"' + @key+ '":'
DECLARE @keyIdx int = CHARINDEX(@keyJson, @data)
IF @keyIdx = 0 RETURN null
DECLARE @valueIdx int = @keyIdx + LEN(@keyJson)
DECLARE @termIdx int = CHARINDEX('"', @data, @valueIdx)
IF @termIdx <> 0 BEGIN
SET @valueIdx = @valueIdx + 1
SET @termIdx = CHARINDEX('"', @data, @valueIdx)
-- Overcome JSON qoute escape
WHILE SUBSTRING(@data, @termIdx-1, 1) = ''
BEGIN
SET @termIdx = CHARINDEX('"', @data, @termIdx + 1)
END
END ELSE BEGIN
SET @termIdx = CHARINDEX(',', @data, @valueIdx)
IF @termIdx = 0 SET @termIdx = CHARINDEX('}', @data, @valueIdx)
END
IF @termIdx = 0 RETURN null
-- Replace escapte quote before return value
RETURN REPLACE(SUBSTRING(@data, @valueIdx, @termIdx - @valueIdx), '"', '"')
END

它仍然有局限性：

• 不支持处理嵌套对象，此外，如果键位于对象深处的某个位置，则可能会产生错误的结果
• 不支持任何东西，但按键返回标量值

以下是一些测试：

-- These work just fine
print [dbo].[GetJsonValue]('foo', '{"foo":"bar"}')
print [dbo].[GetJsonValue]('foo', '{"foo":"Quoted "bar""}')
print [dbo].[GetJsonValue]('foo', '{"foo":55}')
print [dbo].[GetJsonValue]('foo', '{"foo":null}')
print [dbo].[GetJsonValue]('foo', '{"a":"foo","foo":"baz"}')   -- no false positive
-- CANNOT HANDLE SPACES
print [dbo].[GetJsonValue]('foo', '{"foo":   "bar"}')
-- FALSE POSITIVE!!!
print [dbo].[GetJsonValue]('foo', '{"nested:{"foo":123}}')
print [dbo].[GetJsonValue]('foo', '[{"foo":123}]')

输出为：

bar
Quoted "bar"
55
null
baz
123
123