I have a JSON column in a table which looks like this:

{
  "Id": 123 "Filters": [{
        "FilterType": "Category",
        "Values": {
            "23098": "Power Tools",
            "12345": "Groceries"
        }
    }, {
        "FilterType": "Distributor",
        "Values": {
            "98731": "Acme Distribution",
            "12345": "Happy Star Supplies"
        }
    }
  ]
}

Note that the "12345" is in there twice on purpose, because the lookups for distributors might be different than the lookups for Categories, so it's not truly a duplicate, and this is important.

I'm storing this JSON data in a column, and the user has renamed "Groceries" to "Food stuffs". So it's still 12345 but now I want to search and replace Groceries with Foodstuffs in every JSON Field that has a FilterType of Category and ID 12345.

I've figured out how to find the data that has it, but the JSON_MODIFY update statement has me all crossed up because

1. I don't know how to reference a particular key/value pair in json path used in JSON_MODIFY
2. When I do, I still have to specify that the modification should only happen to the value 12345 in the same block as "Category", NOT Distributor.

Can anyone help me construct the T-SQL Magic that would be smart enough to replace the proper key value pair(s) in this? (there could also be a third filter of type Category which also had 12345: Groceries. I omitted it for brevity's sake, but you must assume there could N different filters each of FilterType "Category" and Key = 12345.

CodePudding user response：

You can't use JsonPath predicates, the path must be an exact path.

But in newer versions of SQL Server, you can supply a path from a calculated column.

UPDATE t
SET json = JSON_MODIFY(t.json COLLATE Latin1_General_BIN2, j.path, 'Food stuffs')
FROM YourTable t
CROSS APPLY (
    SELECT TOP (1)
      path = CONCAT('$.Filters[', f.[key], '].Values."', v.[key], '"')
    FROM OPENJSON(t.json, '$.Filters') f
    CROSS APPLY OPENJSON(f.value, '$.Values') v
    WHERE JSON_VALUE(f.value, '$.FilterType') = 'Category'
      AND v.[key] = '12345'
) j;

db<>fiddle

If you have multiple updates to make in each row's JSON value then it gets more complicated and it's often easier to rebuild the JSON using FOR JSON.

CodePudding user response：

So I finally got it working the way I wanted it to. Because of the answers above saying JSON_MODIFY can only work with an exact path, and only on the FIRST occurrence, I do re-run this until @@ROWCOUNT = 0 (or a max # of iterations).

I do both an implicit OPENJSON (to get a key, which doesn't come with explicit OPENJSON), and use an explicit OPENJSON further down to deconstruct the JSON. Then I can reference the precise filter group using the Key, and use the value I passed in as part of the path to find the right key value pair for the JSON_MODIFY.

@TypeID is the int value of the key value pair I want to change within the context I am choosing.

From a performance standpoint, this is actually quite fast, running on our Azure hosted instance of SQL Server and finding and replacing 158 different JSON's in under a second. I can live with this.

Update defs
set defs.data = JSON_MODIFY(defs.data, '$.Filters['   ImplicitFilters.[Key]   '].Values."' CAST(@typeId as varchar) '"', t.[Type] )
from MyTable defs
CROSS APPLY OPENJSON (data , '$.Filters') as ImplicitFilters
CROSS APPLY OPENJSON (ImplicitFilters.Value, '$') 
    WITH (FilterType varchar(50) '$.FilterType', Pairs nVarchar(Max) '$.Values' AS JSON) As Filters
CROSS APPLY OPENJSON( Filters.[Pairs] ) as KeyValuePairs
JOIN dbo.[TypesLookup] t on t.typeid = KeyValuePairs.[key]
where    Filters.CriteriaType = 'Category' 
     and KeyValuePairs.[Key] = @TypeId 
     and KeyValuePairs.[Value] <> t.[name]  -- only change it if it's wrong.