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

Question

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

I don't know how to reference a particular key/value pair in json path used in JSON_MODIFY
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.

There is no T-SQL magic for this. JSON_MODIFY supports absolute paths, but that's about it. There is nothing that will allow sibling filters like XPath, and nothing that constructs paths. Of course you can write it (T-SQL is Turing complete, if we want to get technical) but that doesn't count as magic. What you can do is laboriously deconstruct the objects (using OPENJSON), perform the logic with regular relational logic (CASE WHEN ... THEN 'Foodstuffs' ELSE value END) and then reconstruct things with FOR JSON PATH, but that is very painful. — Jeroen Mostert
– Jeroen Mostert, Commented Jun 15, 2022 at 20:50

Charlieface · Accepted Answer · 2022-06-15 22:45:01Z

1

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.

answered Jun 15, 2022 at 22:45

Charlieface

78.8k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Randy Magruder Over a year ago

Thanks. I'll experiment with your solution.

Randy Magruder · Accepted Answer · 2022-06-16 13:19:07Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related