Previous response (@Isaac and @Slaweek) have some case of failure:

1. If tag/column value is null (in json) return a string value "null" (and not the SQL NULL value) it's not an error, but we are working in sql...
2. If json is an element of an array and is betwwen {...} last tag/column return value + "...}"
3. Same of previous point if json is in "[...]"
4. Tag with "partial name" corresponding are misunderstanding (for example if in your json have "fiscal_address": ... and below "address": ... and you are searching for "address" return the value of "fiscal_address" because contain "address")
5. Commas in value are interpreted as a separated tag (example "price": "1,456.00")

Point nr 5 is not easy to solve: if the tag value is a text where comma/quote are important part of it ? (off course we can count quotes "... but we need to look at escape chars too)

For fix other points i modify in this way:

create or alter FUNCTION [dbo].[JSON_VALUE]
(
    @JSON NVARCHAR(3000),           -- contain json data
    @tag NVARCHAR(3000)             -- contain tag/column that you want the value
)
RETURNS NVARCHAR(3000)

AS
BEGIN

DECLARE @value NVARCHAR(3000);
DECLARE @trimmedJSON NVARCHAR(3000);

DECLARE @start INT
        , @end INT
        , @endQuoted int;

set @start = PATINDEX('%"' + @tag + '":%',@JSON) + LEN(@tag) + 3;
SET @trimmedJSON = SUBSTRING(@JSON, @start, LEN(@JSON));
Set @end = CHARINDEX(',',@trimmedJSON);
if (@end = 0)
    set @end = LEN(@trimmedJSON);
set @value = SUBSTRING(@trimmedJSON, 0, @end);

-- if is present a double-quote then the comma is not the tag-separator
if (len(@value) - len(replace(@value,'"','')) = 1)
begin
    set @endQuoted = CHARINDEX(',',  substring(@trimmedJSON, @end +1, LEN(@trimmedJSON) - @end +1))
    set @value = SUBSTRING(@trimmedJSON, 0, @endQuoted+@end);
end

SET @value = replace(@value,'"','');
-- remove last char if is a ]
IF (RIGHT(RTRIM(@VALUE), 1) = ']')
    SET @value = LEFT(RTRIM(@VALUE), LEN(RTRIM(@VALUE)) -1);
-- remove last char if is a }
IF (RIGHT(RTRIM(@VALUE), 1) = '}')
    SET @value = LEFT(RTRIM(@VALUE), LEN(RTRIM(@VALUE)) -1);

-- if tag value = "null" then return sql NULL value
IF UPPER(TRIM(@value)) = 'NULL'
    SET @value = NULL;

RETURN @value
END