CODEWITHSUNDEEP
jsonsql-servert-sql
Randy Magruder
Randy Magruder

Reputation: 171

T-SQL: Using JSON_MODIFY to modify key value pairs with a filter

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.

Upvotes: 0

Views: 279

Answers (2)

Randy Magruder
Randy Magruder

Reputation: 171

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.

Upvotes: 0

Charlieface
Charlieface

Reputation: 71578

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.

Upvotes: 1

Related Questions