Conditionally update JSON column

Question

I have a table which has ID & JSON columns. ID is auto incrementing column. Here are my sample data.

Row 1

1  |  {
          "HeaderInfo": 
          {
              "Name": "ABC",
              "Period": "2010",
              "Code": "123"
          }, 
          "HData": 
              [
                 { "ID1": "1", "Value": "$1.00", "Code": "A", "Desc": "asdf" }, 
                 { "ID1": "2", "Value": "$1.00", "Code": "B", "Desc": "pqr" }, 
                 { "ID1": "3", "Value": "$1.00", "Code": "C", "Desc": "xyz" }
              ]
      }

Row 2

2  | { 
         "HeaderInfo": 
         {
             "Name": "ABC",
             "Period": "2010",
             "Code": "123"
         }, 
         "HData": 
             [
                 { "ID1": "76", "Value": "$1.00", "Code": "X", "Desc": "asdf" },
                 { "ID1": "25", "Value": "$1.00", "Code": "Y", "Desc": "pqr" }, 
                 { "ID1": "52", "Value": "$1.00", "Code": "Z", "Desc": "lmno" }, 
                 { "ID1": "52", "Value": "$1.00", "Code": "B", "Desc": "xyz" }
             ]
     }

and it keep goes. Items inside the HData section is infinite. It can be any numbers of items.

On this JSON I need to update the Value = "$2.00" where "Code" is "B". I should be able to do this with 2 scenarios. My parameter inputs are @id=2, @code="B", @value="$2.00". @id sometimes will be null. So,

1. If @id is null then the update statement should go through all records and update the Value="$2.00" for all items inside the HData section which has Code="B".
2. If @id = 2 then the update statement should update only the second row which Id is 2 for the items which Code="b"

Appreciate your help in advance.

Thanks

Answer 1

See DB Fiddle for an example.

declare @id bigint = 2
  , @code nvarchar(8) = 'B'
  , @value nvarchar(8) = '$2.00'

update a
set json = JSON_MODIFY(json, '$.HData[' + HData.[key] + '].Value', @value)
from so75416277 a
CROSS APPLY OPENJSON (json, '$.HData') HData
CROSS APPLY OPENJSON (HData.Value, '$')
  WITH (
      ID1 bigint
    , Value nvarchar(8)
    , Code nvarchar(8)
    , [Desc] nvarchar(8)
  ) as HDataItem
WHERE id = @id
AND HDataItem.Code = @Code

• The update / set statement says we want to replace the value of json with a new generated value / functions exactly the same as it would in any other context; e.g. update a set json = 'something' from so75416277 a where a.column = 'some condition'
• The JSON_MODIFY does the manipulation of our json.
  • The first input is the original json field's value
  • The second is the path to the value to be updated.
  • The third is the new value
• '$.HData[' + HData.[key] + '].Value' says we go from our JSON's root ($), find the HData field, filter the array of values for the one we're after (i.e. key here is the array item's index), then use the Value field of this item.
• key is a special term; where we don't have a WITH block accompanying our OPENJSON statement we get back 3 items: key, value and type; key being the identifier, value being the content, and type saying what sort of content that is.
• CROSS APPLY allows us to perform logic on a value from a single DB rowto return potentially multiple rows; e.g. like a join but against its own contents.
• OPENJSON (json, '$.HData') HData says to extract the HData field from our json column, and return this with the table alias HData; as we've not included a WITH, this HData column has 3 fields; key, value, and type, as mentioned above (this is the same key we used in our JSONMODIFY).
• The next OPENJSON works on HData.Value; i.e. the contents of the array item under HData. Here we take the object from this array (i.e. that's the root from the current context; hence $), and use WITH to parse it into a specific structure; i.e. ID1, Value, Code, and Desc (brackets around Desc as it's a keyword). We give this the alias HDataItem.
• Finally we filter for the bit of the data we're interested in; i.e. on id to get the row we want to update, then on HDataItem.Code so we only update those array items with code 'B'.

Answer 2

Try the below SP.

CREATE PROC usp_update_75416277
(
    @id     Int = null, 
    @code   Varchar(15), 
    @value  Varchar(15)
)
AS
BEGIN   
    SET NOCOUNT ON;
    DECLARE @SQLStr Varchar(MAX)=''

    ;WITH   CTE
    AS
    (   SELECT  ROW_NUMBER()OVER(PARTITION BY YourTable.Json ORDER BY (SELECT NULL))RowNo,*
        FROM    YourTable 
        CROSS APPLY OPENJSON(YourTable.Json,'$.HData') 
        WITH (
            ID1     Int         '$.ID1',
            Value   Varchar(20) '$.Value',
            Code    Varchar(20) '$.Code',
            [Desc]  Varchar(20) '$.Desc'
        )   HData 
        WHERE   (@id IS NULL OR ID =@id)
        
    )
    SELECT    @SQLStr=@SQLStr+' UPDATE  YourTable 
                                SET     [JSON]=JSON_MODIFY(YourTable.Json,
                                                            ''$.HData['+CONVERT(VARCHAR(15),RowNo-1)+'].Value'',
                                                            '''+CONVERT(VARCHAR(MAX),@value)+''') '+
                                'WHERE ID ='+CONVERT(Varchar(15),CTE.ID) +' ' 
        
    FROM    CTE
    WHERE   Code=@code
        AND (@id IS NULL OR ID =@id)

    EXEC( @SQLStr) 

END