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;