JSON_MODIFY: How set value for not exists path?-CodePudding

SELECT JSON_MODIFY('{}', '$.user.setting.theme', 'white')

Results

{}

We are losing new value. How to prevent?

CodePudding user response：

JSON_MODIFY: How set value for not exists path?

If the path does not exists then you need to build it from the top level down.

You have the option to add each value/node starting from the top level one after the other, or you can add the entire path at once using JSON_Query.

For example according to your attempt to execute SELECT JSON_MODIFY('{}', '$.user.setting.theme', 'white'), I assume that you want to get the following result

{
  "user": {
    "setting": {
      "theme": "white"
    }
  }
}

In your attempt you use the path $.user.setting.theme which does not exists so nothing is changed. The top level path that does exists is $. and the first level which we want to add is the node user, therefore we will use the path '$.user'.

Now we can add the value of this new node.

So the solution will be:

------------------ Option 1: build step by step (level after level)
DECLARE @J NVARCHAR(MAX)
SET @J = '{}'
SELECT @J
SET @J = JSON_MODIFY(@J, '$.user', JSON_Query('{}'))
SELECT @J
SET @J = JSON_MODIFY(@J, '$.user.setting', JSON_Query('{}'))
SELECT @J
SET @J = JSON_MODIFY(@J, '$.user.setting.theme', 'white')
SELECT @J

------------------ Option 2: build in one
SELECT JSON_MODIFY('{}', '$.user', JSON_Query('{"setting":{"theme":"white"}}}'))

CodePudding user response：

I'm created new function [dbo].[JSON_PATH_MODIFY] that do it work very well now

/*
SELECT [dbo].[JSON_PATH_MODIFY]('{ "user" : { "name": "John" } }', '$.user.setting.theme', 'white')
*/
ALTER FUNCTION [dbo].[JSON_PATH_MODIFY](@json nvarchar(max), @path nvarchar(max), @value nvarchar(max))
RETURNS nvarchar(max) 
AS
BEGIN
    DECLARE @t_path nvarchar(max) = @path, @p_path nvarchar(max)
    SET @t_path = SUBSTRING(@t_path, 1, LEN(@t_path) - CHARINDEX('.', REVERSE(@t_path)))
    
    DECLARE @i_word varchar(max), @p_json nvarchar(max) = '{}'

    WHILE JSON_QUERY(@json, @t_path) IS NULL BEGIN
        SET @i_word = REVERSE(SUBSTRING(REVERSE(@t_path), 1, CHARINDEX('.', REVERSE(@t_path)) - 1))

        SET @p_path = @t_path
        SET @t_path = SUBSTRING(@t_path, 1, LEN(@t_path) - CHARINDEX('.', REVERSE(@t_path)))

        SET @p_json = CASE WHEN JSON_QUERY(@json, @t_path) IS NULL THEN JSON_MODIFY('{}', '$.'  @i_word, JSON_QUERY(@p_json))
            ELSE JSON_MODIFY(@json, @p_path, JSON_QUERY(@p_json)) END
    END

    RETURN JSON_MODIFY(@p_json, @path, @value)
END

Results

{ "user" : { "name": "John" ,"setting":{"theme":"white"}} }