sql-server json sql-server-2016: Updated Tue, 03 May 2022 11:40:03 GMT

Can SQL Server 2016 validate JSON on insert?

Currently SQL Server 2016 CTP 3 and later releases support NVARCHAR(max) fields with JSON content. However since the type NVARCHAR(max) is totally free-form, there is no constraint on these fields.

Can I declare a constraint that prevents insertion of a non-null value that is not valid JSON?

Solution

Add this constraint:

ALTER TABLE [dbo].[Resource] 
 ADD CONSTRAINT [RESOURCE_ExtJSON_VALID] 
   CHECK ( ISJSON( ExtJSON )> 0 )

References

https://dba.stackexchange.com/questions/128764