SQL Server 2012 的 JSON_VALUE? [英] JSON_VALUE for SQL Server 2012?

问题描述

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

I want to extract values from a JSON string in SQL. Started to write a function to do so but then I thought "surely someone else has already done this?". I was super excited when I saw there is a JSON_VALUE function in SQL Server now... but then sorely disappointed when I realized it wasn't added until 2016. :(

所以...我正在编写我自己的这个函数版本.我敢肯定它一开始看起来会奏效，然后我会偶尔出错，直到我随着时间的推移完善它.

So... I am in the middle of writing my own version of this function. I'm certain it will seem to work at first, and then I will get errors occasionally until I perfect it over time.

但我希望有人已经在这方面取得了领先，并找出了一些我在初稿中肯定会忽略的问题......并希望这里有人能指点我一下?

But I'm hoping someone has already gotten a head start on this and worked out a few of the kinks that I will certainly overlook in my first draft... and hoping someone here can point me to it?

推荐答案

好吧，既然似乎还没有人提供任何东西，这里是我迄今为止编写的代码.也许它会帮助我的下一个人.我决定根据我要检索的值的类型使用单独的函数.需要特别注意的是，date 函数用于检索一个值，该值是自 1970 年以来的毫秒数，decimal 函数有一个参数来指定该值是否被引用.

Well since it seems no one has had anything to offer yet, here is the code I've written so far. Maybe it will help the next person in my shoes. I decided to go with separate functions depending on the type of value I'm retrieving. Of special note is that the date function is for retrieving a value that is the number of milliseconds since 1970, and the decimal function has a parameter to specify whether the value is quoted or not.

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

这篇关于SQL Server 2012 的 JSON_VALUE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！