Storing non-json data into a column with type VARCHAR(N)/NVARCHAR(N), which is used for a JSON property index, results in an error

Question

I've a problem storing non-json data into a column which is used for a JSON property index. The column is of type VARCHAR(2550).
After the index has been created, storing non-json data into the column results in the following error: [S0001][13609] Line 1: JSON text is not properly formatted. Unexpected character 'n' is found at position 0.

If the type of the column would be VARCHAR(MAX) or NVARCHAR(MAX) storing non-json data would work.

E.g. if you adapt the SQL from the example to create the column containing JSON like this:

--- create column Info with type NVARCHAR(4000) containing JSON  
IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('[Sales].[SalesOrderHeader]') AND name = 'Info')  
	ALTER TABLE [Sales].[SalesOrderHeader] ADD [Info] NVARCHAR(4000) NULL  
GO  
UPDATE h   
SET [Info] =  
(  
    SELECT [Customer.Name]	= concat(p.FirstName, N' ', p.LastName),   
           [Customer.ID]	= p.BusinessEntityID,   
           [Customer.Type]	= p.[PersonType],   
           [Order.ID]		= soh.SalesOrderID,   
           [Order.Number]	= soh.SalesOrderNumber,   
           [Order.CreationData] = soh.OrderDate,   
           [Order.TotalDue]	= soh.TotalDue  
    FROM [Sales].SalesOrderHeader AS soh  
         INNER JOIN [Sales].[Customer] AS c ON c.CustomerID = soh.CustomerID  
         INNER JOIN [Person].[Person] AS p ON p.BusinessEntityID = c.CustomerID  
    WHERE soh.SalesOrderID = h.SalesOrderID FOR JSON PATH, WITHOUT_ARRAY_WRAPPER   
)  
FROM [Sales].SalesOrderHeader AS h;   
  
-- create computed column and index  
ALTER TABLE Sales.SalesOrderHeader  
ADD vCustomerName AS JSON_VALUE(Info,'$.Customer.Name')  
  
CREATE INDEX idx_soh_json_CustomerName  
ON Sales.SalesOrderHeader(vCustomerName)  

Then trying to store non-json data in the Info column (e.g. UPDATE Sales.SalesOrderHeader SET Info = 'null') results in the error mentioned above.

Is this behavior a bug or by design? If it's by design it should be mentioned in the docs IMHO.

Answer 1

I think that the claims of SQL Server seem legitimate, because JSON_VALUE requires a valid JSON.

The behaviour in case of VARCHAR(MAX) or NVARCHAR(MAX) was not reproduced.

You can try the following computed column:

ALTER TABLE Sales.SalesOrderHeader  
ADD vCustomerName AS case when ISJSON(Info)=1 then JSON_VALUE(Info,'$.Customer.Name') end  

The value will be NULL in case of invalid JSON.