SQL Server : Json Column Query/Select Based On Value Within Json Array

The JSON data in the question is a JSON array, so you need to parse this JSON with OPENJSON() and explicit schema:

Table:

CREATE TABLE Data (
   JsonData nvarchar(max),
   [Name] nvarchar(100), 
   Address nvarchar(100)
)
INSERT INTO Data (
   JsonData, [Name], [Address])
VALUES (
   N'[
{"Key":"priorityProduct","Value":"0"},
{"Key":"somethingElse","Value":"another value"},
{"Key":"remove","Value":"1"},
{"Key":"anotherHere","Value":"27"}
]', N'Name', N'Address')

Statement:

SELECT d.[Name], d.[Address], j.[Value] AS [Remove]
FROM Data d
CROSS APPLY OPENJSON(d.JsonData) WITH (
   [Key] nvarchar(100) '$.Key',
   [Value] nvarchar(100) '$.Value'
) j
WHERE j.[Key] = N'remove'

Result:

----------------------
Name    Address Remove
----------------------
Name    Address 1

If you want to use JSON_VALUE(), the next statement may help. Note, that JSON_VALUE() returns a single text value of type nvarchar(4000):

SELECT d.[Name], d.[Address], JSON_VALUE(j.[value], '$.Value') AS [Remove]
FROM Data d
CROSS APPLY OPENJSON(d.JsonData) j
WHERE JSON_VALUE(j.[value], '$.Key') = N'remove'

Of course, if this JSON has a fixed structure and remove key is always at 3rd position, you may use something like this:

SELECT [Name], [Address], JSON_VALUE(JsonData, '$[2].Value') AS [Remove]
FROM Data