Append using JSON_MODIFY into a blank column fails with "Unexpected character '.' is found at position 0" error

user_303305 0
Viewed 176

I have a SQL Server table with a NVARCHAR(MAX) NOT NULL column with a default of empty string. The check constraint checks if the content is valid JSON if it is not null or an empty string:

CHECK ((ISNULL(ISJSON(NULLIF([MyJSONColumn],'')), (1)) = (1)

If the column already contains some JSON, for example, the following:

["This is some original stuff"]

then I can append to it using JSON_MODIFY.

For example, this works:

UPDATE t
SET t.MyJSONColumn = JSON_MODIFY(t.MyJSONColumn, 'append $', '"This is some appended stuff."')
FROM dbo.MyTable t
WHERE t.PrimaryKey = 1

and if I then select from the table:

SELECT MyJsonColumn 
FROM dbo.MyTable 
WHERE PrimaryKey = 1

I get my appended JSON, like so:

["This is some original stuff","This is some appended stuff."]

All is well. However, if I attempt the exact same JSON_MODIFY update when the column is an empty string, I get this error:

JSON text is not properly formatted. Unexpected character '.' is found at position 0.

The only way I can initially set the value of a blank MyJSONColumn is to do something like this. This works, but is of course very ugly:

UPDATE t
SET t.MyJSONColumn = '["This is some stuff."]')

After I get that initial value in the column, I can then use JSON_MODIFY to append to it.

Can I use JSON_MODIFY to set that initial value? I've tried adding the "lax" modifier, but I still get the error (ie 'append lax $').

sql-server json
2 Answers

I would recommend to change your column's default, as empty string is not a valid JSON. As such, when you pass it as the first parameter into the JSON_MODIFY() function, it predictably disagrees.

Depending on your needs, it might be a root array:

declare @s nvarchar(max) = '[]';
set @s = JSON_MODIFY(@s, 'append $', N'This is some appended stuff.');
select @s;

or a more elaborated (but extensible) structure:

declare @s nvarchar(max) = '{ "Values" : [] }';
set @s = JSON_MODIFY(@s, 'append $.Values', N'This is some appended stuff.');
select @s;

P.S. Your double quotes that you pass as a part of your value become escaped; if you don't need that, remove them from the incoming value, as in my examples.

user_39252320

The problem is that your constraint is wrong.

'' empty string is simply not valid JSON, but the constraint allows it, because of the NULLIF. The constraint should be

CHECK (ISJSON([MyJSONColumn]) = (1))

and then '' will not be allowed.

The only issue you can have is that JSON_MODIFY append will not work on a null value. Instead, you can use a default value of [] which is perfectly valid JSON as an empty array.

Note that ISNULL is also not necessary, because a CHECK constraint will pass a NULL return from the check, as mentioned in the documentation.

db<>fiddle

user_148689970
Related