Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

I have a column that has the following JSON in it (this is a small sample, there are a lot of other rows).

{ "Key": "priorityProduct", "Value": "0" }, { "Key": "somethingElse", "Value": "another value" }, { "Key": "remove", "Value": "1" }, { "Key": "anotherHere", "Value": "27" }

I want to SELECT the value of the remove only in a SQL query and ignore the other values. So I could SELECT some SQL columns to say name , address and use something like this to fill the remove ?

SELECT 
    [name], address, 
    JSON_VALUE(MyColumn, '$[Key:"remove"].Value') AS Remove
Does such a query syntax exist?
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 (
{"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'
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
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.