Add new value to json string in SQL Server 2016

Question

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

When you say you don't want to use json_modify, do you mean you want to use string manipulation instead / what's your reason for not using json_modify? — JohnLBevan
– JohnLBevan, Commented Apr 29, 2018 at 8:27
Actually i would like to create a procedure like json_modify buy using common SQL constructs. This is a exercise for me... — Ali
– Ali, Commented Apr 29, 2018 at 8:32
I don't know if it is necessary to use sql string manipulation.... i can imagine, that it is also possible to select from json objects by using the OPENJSON Function and then union with the new json object. Afterwars it should be possible to use FOR JSON AUTO or FOR JSON PATH to get back a single manipulated json string... — Ali
– Ali, Commented Apr 29, 2018 at 8:36
open json will return a generic table of (key - type - value) unless you specify the keys. string manipulation is probably going to result in quite a cumbersome, slow performing code. I would suggest using actual, real-world solved problems as exercise base. Helping other people with their problems on Stackoverflow has been a great learning tool for me personally. I've learned a lot by answering questions - I would even say more then I've learned by asking questions here. — Zohar Peled
– Zohar Peled, Commented Apr 29, 2018 at 8:42
Do you think it is possible to see the implementation of json_modify anywhere? — Ali
– Ali, Commented Apr 29, 2018 at 8:44

user1941574 · Accepted Answer · 2019-01-18 15:15:00Z

2

    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.

answered Jan 18, 2019 at 15:15

user1941574

1651 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ali · Accepted Answer · 2019-01-26 10:10:54Z

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.

Collectives™ on Stack Overflow

Add new value to json string in SQL Server 2016

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related