SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE FUNCTION dbo.my_func(@s NVARCHAR(MAX))
RETURNS NVARCHAR(100)
WITH SCHEMABINDING
AS
BEGIN
   DECLARE @r NVARCHAR(100);
   
   SELECT @r = Value 
   FROM OPENJSON(@s,'$.Response.DataArray')  
   WITH ([Type] NVARCHAR(100) '$.Type', [Value] NVARCHAR(100) '$.Value')
   WHERE [Type] = 'Name';
   
   RETURN @r;
END;
CREATE TABLE tab(val NVARCHAR(MAX) CHECK (ISJSON(val) = 1),
 col1 AS dbo.my_func(val) PERSISTED
  );
INSERT INTO tab(val) VALUES (N'{
   "Response":{
      "DataArray":[
         {
            "Type":"Address",
            "Value":"123 Fake St"
         },
         {
            "Type":"Name",
            "Value":"John Doe"
         }
      ]
   }
}')
1 rows affected

SELECT * FROM tab;


val
col1


{
   "Response":{
      "DataArray":[
         {
            "Type":"Address",
            "Value":"123 Fake St"
         },
         {
            "Type":"Name",
            "Value":"John Doe"
         }
      ]
   }
}
John Doe


CREATE INDEX idx ON tab(col1);