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
– abolfazl sadeghi, Commented Apr 16, 2023 at 14:17
You Can use link useful below --- stackoverflow.com/questions/56996587/… learn.microsoft.com/en-us/sql/t-sql/functions/… stackoverflow.com/questions/57107947/… — abolfazl sadeghi
– abolfazl sadeghi, Commented Apr 16, 2023 at 16:55
Openjson&recursion and select @=json_modify(@).. dbfiddle.uk/gUgqyF7f — lptr
– lptr, Commented Apr 16, 2023 at 17:08
@lptr That relies on variable coalescing, which is documented in a number of places to be unreliable stackoverflow.com/questions/15138593/… learn.microsoft.com/en-us/sql/t-sql/language-elements/… — Charlieface
– Charlieface, Commented Apr 16, 2023 at 17:17

Charlieface · Accepted Answer · 2023-04-16 17:14:35Z

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

Collectives™ on Stack Overflow

SQL Server Json iterating and updating regardless root

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related