SQL Server function for getting values from JSON
Recently, I needed to build a report based on data in a SQL Server table that had as one of its fields some JSON data. The report was going into Excel, and initially, I was just parsing the JSON and putting in every key/value pair into the spreadsheet. (I did all that in C#.) I found that the client only wanted a few discrete fields from the JSON, so I instead wrote this SQL Server function that will get the value based on the input JSON "field":
USE utility;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
IF OBJECT_ID('dbo.GetJSONValue') IS NOT NULL
EXEC ('drop FUNCTION dbo.GetJSONValue;')
GO
CREATE FUNCTION dbo.GetJSONValue(
@json varchar(max),
@fieldName varchar(255)) RETURNS varchar(255) AS
BEGIN
declare @fieldStart int;
declare @startIndex int;
declare @nextValueIndex int;
declare @length int;
declare @value varchar(255);
declare @valueReversed varchar(255);
set @fieldStart = charindex(@fieldName, @json);
if @fieldStart <= data-preserve-html-node="true" 0
begin
set @value = null;
end;
else
begin
set @startIndex = charindex(@fieldName, @json)+len(@fieldName)+2;
set @nextValueIndex = charindex(':', @json, @startIndex);
set @valueReversed = reverse(substring(@json, @startIndex, @nextValueIndex - @startIndex));
set @length = len(@valueReversed) - charindex(',', @valueReversed);
set @value = replace(substring(@json, @startIndex, @length),'"','');
end;
return @value;
end;
GO
grant execute on getjsonvalue to public;