CODEWITHSUNDEEP
sqljsonsql-server
Ali
Ali

Reputation: 480

Add new value to json string in SQL Server 2016

I would like to insert new values into a random Json string without using the Json_modify function. I am using SQL Server 2016.

For example, let's say I have a Json string like this: 

{ "a": { "b": "val"} }

Now I would like to add a new object into the Json string. After adding a object c with value NEW the Json string should look like this: 

{ "a": { "b": "val", "c": "NEW"} }

Is there any way to do something like this without using json_modify?

I do not want to save the Json string into a table. All I want afterwards is a SQL Server procedure which adds a Json object into a Json string on a defined position.

Thanks

How I'd do this with JSON_MODIFY:

declare @jsonString nvarchar(4000) = '{"a": {"b":"val"} }'
, @jsonResult nvarchar(4000)
, @path nvarchar(128) = '$.a.c'
, @value nvarchar(128) = 'NEW'

set @jsonResult = JSON_MODIFY(@jsonString, @path, @value)
select @jsonResult

Upvotes: 2

Views: 2424

Answers (2)

Ali
Ali

Reputation: 480

Thanks for your answers and comments. I've already solved the problem, but couldn't solve the stackoverflow question because I'm using foreign libraries. Either way, it was just an exercise for me. In the end, I spent four months dealing with this problem and found a non-trivial solution. As I said before, I can't post it here because I'm embedding foreign code and the author of the foreign code didn't allow me to post it publicly.

Basically it was about editing arbitrary JSON objects in any depth in TSQL. In other words, adding new elements, deleting elements and changing elements and returning the changed JSON object as string. I also didn't want to use the function "JSON_MODIFY" provided by Microsoft.

I intentionally did not want to use JSON_MODIFY because this method is only available in newer SQL Server versions and not in older versions. There are certainly enough developers who would like to edit JSON objects in TSQL but have older SQL Server versions and don't want to buy a new SQL Server license just for this one function. For these people my solution would have been very helpful. But unfortunately not publishable, because foreign code was integrated.

Many thanks to everyone who helped here.

Upvotes: 2

user1941574
user1941574

Reputation: 167

    DECLARE @json VARCHAR(MAX)='{ "a": { "b": "val"} }'

    --Collect values in tmp table
    SELECT * INTO #tbljson  FROM OPENJSON(@json,'$.a') WITH ( b VARCHAR(50))

    -- Add new column into tmp table
    ALTER TABLE #tbljson
    ADD c VARCHAR(10) NULL

    --Update value to new column
    UPDATE #tbljson SET c= 'New' WHERE b='val'

    -- get new Json 
    DECLARE @newJson VARCHAR(max)
    SET @newJson=(SELECT * FROM #tbljson FOR JSON PATH, INCLUDE_NULL_VALUES)


    -- Update it to original Json.
    SET @json= JSON_MODIFY(@json, '$.a',JSON_QUERY(@newJson))

    SELECT @json
    DROP TABLE #tbljson

This could be my solution to the issue. Instead of string operation, I would do this in case Json collection is little bigger, then I would do this. This is cleaner approach then string operation.

Upvotes: 2

Related Questions