Update an existing JSON value inside a JSON Array in SQL

Question

I want to update an existing JSON value inside a JSON array. I can append a new JSON to the JSON array using JSON_MODIFY. Suppose i have a JSON like :

[{"id":"101","name":"John"}, {"id":"102","name":"peter"}]

But i want to update only the json with id=102.

Is it possible using JSON_MODIFY()?

EDIT:

Actual data

{"Details":{"SId":{"Type":"string","Value":"1234"},"BookList":{"Type":"List","Value":[{"id": "101", "name": "Book1"},{"id": "102", "name": "Book2"}]},"SName":{"Type":"string","Value":"john"}}}

Please do not ever edit question in a way that invalidates existing answers. If you need to add some details mark it clearly or ask new question — Lukasz Szozda, Apr 16 '18 at 14:07

score 20 · Answer 1 · answered Apr 14 '18 at 07:29

20

You could use CTE to parse it and combine path in UPDATE part:

WITH cte AS (
  SELECT *
  FROM t
  CROSS APPLY OPENJSON(c) s
  WHERE i = 1
    AND JSON_VALUE(s.value, '$.id')=102
)
UPDATE cte
SET c = JSON_MODIFY(c, '$[' + cte.[key] + '].name', 'Joe');

DBFiddle Demo

Output:

-- Before
[{"id":"101","name":"John"}, {"id":"102","name":"peter"}]

-- After
[{"id":"101","name":"John"}, {"id":"102","name":"Joe"}]

This will work on SQL Server 2017+ or SQL Azure DB otherwise you will get error. More info about path literal

answered Apr 14 '18 at 07:29

Lukasz Szozda

162,964
23
234
275

I have given my actual json format.How can i use cte and cross aply there? – Santanu Apr 16 '18 at 07:00
Can you please suggest a solution that works in versions below SQL Server 2017? i'm getting `The argument 2 of the "JSON_MODIFY" must be a string literal.` error. – Jishad Sep 25 '18 at 10:51
3

@Jishad, you could generate your update statements. Something like `select 'update [yourtable] set [jsoncol] = json_modify([jsoncol], ''$[' + [key] + '].name'', ''Joe'') where id = ' + [yourtable].id from [yourtable] cross apply openjson([jsoncol])` and then execute them all with sp_executesql (of course, being wary of sql injection) – JohnnyFun Dec 22 '18 at 00:08
1

Wouldn't believe how hard I've hunted for this answer, much love – jolySoft Jul 21 '21 at 11:30

score 1 · Answer 2 · answered Jun 10 '21 at 13:38

Updating JSON Data (Postgresql)

If the column in your table contains json data and you want to update this data, you can use the following structure:

UPDATE table_name SET column_name = '{"key" : value}'::jsonb 
WHERE column_name::jsonb @> '{“new_key” : new_value}'::jsonb;

Note: Usually @> is used as the "contains" operator.

score 0 · Answer 3 · edited Jul 31 '22 at 02:18

The best way is to generate the statement like this:

In this way you, won't get the error of "Cannot resolve the collation conflict between Latin1_General_BIN and SQL_Latin1_General_CP1_CI_AS"

Also, you won't get this error "The argument 2 of the JSON_MODIFY must be a string literal"

WITH cte AS (
  SELECT 
  t.PrimaryKey,
  JSON_VALUE([value], '$.id') as id,
  t.JsonColumn,
  o.*
  ,('UPDATE MyTable set JsonColumn = JSON_MODIFY(JsonColumn, ''$['+[key]+'].id'', ''NewVALUE'') WHERE PrimaryKey = '''+t.PrimaryKey COLLATE SQL_Latin1_General_CP1_CI_AS+ '''') as statement
  FROM MyTable t
   CROSS APPLY OPENJSON(JSON_QUERY(JsonColumn, '$')) o WHERE  JSON_VALUE(o.value, '$.Id')= 1
   )
select * from cte;

score 0 · Answer 4 · answered Jun 22 '23 at 09:47

Do like this:

Declare @Input nvarchar(max) = N'[{"id":"101","name":"John"}, {"id":"102","name":"peter"}]';
Declare @UpdatedItem nvarchar(max) = N'{"id":"102","name":"peter1"}'

SET @Input = JSON_MODIFY(@Input, CONCAT('$[', (SELECT [Key] FROM OPENJSON(@Input) as I WHERE JSON_VALUE(I.Value,'$.id') = "102"), ']'), JSON_QUERY(@UpdatedItem));

Update an existing JSON value inside a JSON Array in SQL

4 Answers4