What's the fastest way to search for a value in json in SQL, if we do not know the path

Question

I am looking for the fastest way to search in a SQL table for a value in a JSON.

I am trying to update this value. And there may be more occurrences in the same JSON.

update d
    set d.Content = replace(d.Content, rm.WrongValue , rm.NewValue)
    from dbo.Document d
    inner join RenameMatches rm d.content like '%' + rm.WrongValue + '%'

The next line is taking to long and I am trying to make it faster

 d.content like '%' + rm.WrongValue + '%'

I thought using JSON_Modify, however, the path is not always the same.

Thanks!

Answer 1

If there's multiple WrongValue's in that RenameMatches, then you could loop through them.

But then it needs some sequencial number without gaps.

In the example below, the values are assumed to be strings.
So they get double-quoted to match complete string values.

Demo:

-- 
-- Sample data
-- 
create table Document (
 document_id int identity primary key, 
 content nvarchar(max)
);

insert into Document (content) values
  ('[{"id":1, "code":"abc123"},{"id":2, "code":"def456"}]') 
, ('{"id":3, "data":{"name":"test",code1":"abc123","code2":"abc123"}}');

create table RenameMatches (
 WrongValue nvarchar(30) not null primary key, 
 NewValue nvarchar(30) not null
);

insert into RenameMatches (WrongValue, NewValue) values
  ('abc123', 'abc12345')
, ('def456', 'def456789');

-- Using a table variable with identity
DECLARE @RenameMatches table (
 id INT identity primary key, 
 WrongValue NVARCHAR(30) not null, 
 NewValue NVARCHAR(30) not null
);

INSERT INTO @RenameMatches (WrongValue, NewValue) 
SELECT WrongValue, NewValue
FROM RenameMatches;

DECLARE @MaxRenameID INT = (SELECT MAX(id) FROM @RenameMatches);
DECLARE @RenameID INT = 1;

WHILE @RenameID <= @MaxRenameID
BEGIN
   UPDATE d
   SET d.Content = REPLACE(d.Content
                   , quotename(rm.WrongValue,'"') 
                   , quotename(rm.NewValue,'"')) 
   FROM Document d
   JOIN @RenameMatches rm
     ON rm.id = @RenameID 
    AND d.content like '%"'+rm.WrongValue+'"%';

   SET @RenameID += 1;
END;

select * from Document

document_id	content
1	[{"id":1, "code":"abc12345"},{"id":2, "code":"def456789"}]
2	{"id":3, "data":{"name":"test",code1":"abc12345","code2":"abc12345"}}

Demo on db<>fiddle here