JSON_VALUE for SQL Server 2012?

Question

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?

Answer 1

First of all, thank you @BVernon - your answer got me on right track. I have improved version of this - it is capable of:

• returning strings, numbers or null and handle
• handles JSON quote escaping
• a bit better/robust finding - in case key is part of some other string value, it searches by "key":, not just by 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

It still have limitations:

• handling nested objects is not supported, moreover it could give false results, if the key is somewhere deep in object
• does not support anything but returning scalar value by key

These are some tests:

-- 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}]')

Output is:

bar
Quoted "bar"
55
null
baz

123
123

Answer 2

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