SQL Server Json iterating and updating regardless root

Question

Is that possible in SQL Server JSON to update elements with a certain attribute value regardless root, position and nesting level?

For example we have multiple elements like

{ "name": "target_name", "value": 123 }

They can appear on any level within a random json string, so is that possible to increment value number for elements with target_name?

Thanks!

PS: we can't install any DB extensions due to Azure limitations, so it should be done using native SQL.

UPD: JSON example:

{
    "name": "target_name",
    "value": 1,
    "some_arr": [
        {
            "foo": "foo",
            "name": "foo_name"
        }, 
        {
            "name": "target_name",
            "value": 5
        }, 
        {
            "foo2": "foo",
            "name": "some_name"
            "foo3": {
                "name": "target_name",
                "value": 7
            }
        }
    ]
}

In this example there are three elements with name=="target_name", they are placed as root, in an array, on a deeper nesting level. JSON structure can change. The point is to increment numbers 1, 5, and 7 within "value" attributes using standard means like JSON_MODIFY, OPENJSON, JSON_VALUE.

if you can to use t-sql that you can to update json with JSON_MODIFY,OPENJSON if you can to use t-sql that please add whole json in post that we can solve your problem — abolfazl sadeghi, Apr 16 '23 at 14:17
You Can use link useful below --- https://stackoverflow.com/questions/56996587/update-json-value-from-sql-column-value https://learn.microsoft.com/en-us/sql/t-sql/functions/json-modify-transact-sql?view=sql-server-ver16 https://stackoverflow.com/questions/57107947/update-json-using-json-modify — abolfazl sadeghi, Apr 16 '23 at 16:55
Openjson&recursion and select @=json_modify(@).. https://dbfiddle.uk/gUgqyF7f — lptr, Apr 16 '23 at 17:08
@lptr That relies on variable coalescing, which is documented in a number of places to be unreliable https://stackoverflow.com/questions/15138593/nvarchar-concatenation-index-nvarcharmax-inexplicable-behavior/15163136#15163136 https://learn.microsoft.com/en-us/sql/t-sql/language-elements/select-local-variable-transact-sql?view=sql-server-ver15#c-antipattern-use-of-recursive-variable-assignment — Charlieface, Apr 16 '23 at 17:17

score 2 · Answer 1 · answered Apr 16 '23 at 17:14

You could use OPENJSON to break open a JSON object or array. You can do this in two separate functions, one for objects and another for arrays.

Unfortunately, SQL Server does not support JSON_OBJECT_AGG, which would have made this easier. Instead, we need to use STRING_AGG.

We need to also correctly handle cases where an array or object may have no properties, and where a value may be null, a number, boolean or string.

CREATE FUNCTION dbo.ChangeJsonObject(@json nvarchar(max))
RETURNS nvarchar(max)
AS BEGIN
  DECLARE @name nvarchar(max) = JSON_VALUE(@json, '$.name');
  
  RETURN (
    SELECT CONCAT('{', STRING_AGG(j.[key] + N':' + v.NewValue, ','), '}')
    FROM OPENJSON(@json) j
    CROSS APPLY (VALUES(
        CASE j.type
        WHEN 0 THEN
          N'null'
        WHEN 1 THEN
          N'"' + j.value + N'"'
        WHEN 2 THEN
          IIF(
            j.[key] = 'value' AND @name = 'target_name',
            CAST(CAST(j.value AS int) + 1 AS nvarchar(max)),
            j.value
          )
        WHEN 4 THEN
          dbo.ChangeJsonArray(j.value)
        WHEN 5 THEN
          dbo.ChangeJsonObject(j.value)
        ELSE
          j.value
        END
    )) v(NewValue)
  );
END;

CREATE FUNCTION dbo.ChangeJsonArray(@json nvarchar(max))
RETURNS nvarchar(max)
AS BEGIN
  RETURN (
    SELECT CONCAT('[', STRING_AGG(v.NewValue, ','), ']')
    FROM OPENJSON(@json) j
    CROSS APPLY (VALUES(
        CASE j.type
        WHEN 0 THEN
          N'null'
        WHEN 2 THEN
          N'"' + j.value + N'"'
        WHEN 4 THEN
          dbo.ChangeJsonArray(j.value)
        WHEN 5 THEN
          dbo.ChangeJsonObject(j.value)
        ELSE
          j.value
        END
    )) v(NewValue)
  );
END;

Finally, simply query the table like this

SELECT
  dbo.ChangeJsonObject(d.json)
FROM YourTable d;

db<>fiddle

SQL Server Json iterating and updating regardless root

1 Answers1