I have a SQL Server table with a NVARCHAR(MAX) NOT NULL column with a default of empty string. The check constraint insists the content is valid JSON:
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 $').
CodePudding user response：
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.
CodePudding user response：
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.