SQL - Replace value based on specific object property-CodePudding

I'm trying to write a conditional update statement in SQL that looks at an array of objects, and updates one object property based on another object's property. Below is an example of an object in the signal_key column that I'm trying to update.

{
  "entities": [
    {"type": "MERCHANT", "value": "AAAA"}, 
    {"type": "MERCHANT", "value": "CCC"}], 
  "signalType": "TRANSACTION", 
  "signalVersion": "0"
}

Based on the "value" string, I want to update the "type" from MERCHANT to ID. Is there a way for me to specifically check value instead of using something like LIKE that would look for a string comparison in the whole object? Below, signal_key??? indicates the portion of the query that I am asking about.

UPDATE signals
SET signal_key??? =  CASE
        WHEN signal_key??? = 'AAAA' THEN 'BBBB'
        WHEN signal_key??? = 'CCC' THEN 'DDD'
        ELSE signal_key???
    END
WHERE signal_key??? IN ('AAAA', 'CCC')

CodePudding user response：

Based on the length of the value string

You can use Len(my_field) to check the length

For example:

UPDATE some_table
SET some_field = CASE
        WHEN len(another_field) = 4 THEN 'BBBB'
        WHEN len(another_field) = 3 THEN 'DDD'
        ELSE 0
    END
WHERE 1=1

Edit: To update based on related data:

First create a CTE (common table expression, using with) that contains each item with the length of the associated items. In the example below the CTE contains each item id with the count (or "length") of associated items.

Then issue an update statement that uses the length table joined with the table you want to update.

with lengths (id, length) as (
    select t1.id, count(related.id)
    from test_table_1 t1
    inner join test_table_related_data related on related.parentId = t1.id
    group by t1.id
)
update t1
set t1.name = case when l.length = 1 then 'one' else 'a lot!!!' end
from test_table_1 t1
inner join lengths l on l.id = t1.id

CodePudding user response：

To parse JSON, you should use SQL Server's JSON capabilities.

You need to use OPENJSON in a correlated subquery to find the right index to modify.

If you only want to modify a single value per row, you can do it like this.

UPDATE s
SET signal_key = JSON_MODIFY(
        s.signal_key,
        '$.entities['   j.[key]   '].value',
        'BBBB')
FROM signals s
CROSS APPLY (
    SELECT TOP (1) j.[key]
    FROM OPENJSON(s.signal_key, '$.entities') j
    WHERE JSON_VALUE(j.value, '$.value') = 'AAAA'
) j;

Otherwise you need to rebuild the JSON array

UPDATE s
SET signal_key = JSON_MODIFY(
        s.signal_key,
        '$.entities',
        j.entities)
FROM signals s
CROSS APPLY (
    SELECT
      j.type,
      value = CASE j.value
                WHEN 'AAAA' THEN 'BBBB'
                WHEN 'CCC' THEN 'DDD'
                ELSE j.value
              END
    FROM OPENJSON(s.signal_key, '$.entities')
      WITH (
        type varchar(100),
        value varchar(100)
      ) j
    FOR JSON PATH
) j(entities);