Storing non-json data into a column with type not NVARCHAR(MAX), which is used for a JSON property index, throws an error

Snozzlebert 0
Viewed 20

I've a problem storing non-json data into a column which is used for a JSON property index. 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.

I've created the computed column needed for the index and the index itself like this:

ALTER TABLE foo ADD vBar AS IIF(ISJSON(DETAILS_JSON) > 0, JSON_VALUE(DETAILS_JSON, '$.bar'), NULL)
CREATE INDEX IDX_FOO_BAR_ID ON foo (vBar)

When trying to store non-json data in the column (e.g. UPDATE foo SET bar = 'simple text') it results in the error mentioned above.

However, when I execute the example and store non-json data in the column it works...

sql-server json
1 Answers

The problem was the length of the column where the JSON data is stored. The length has to be MAX - if it is different (e.g. NVARCHAR(4000) or VARCHAR(4000)) it results in the mentioned error.

I. e. the type of the column has to be NVARCHAR(MAX) or VARCHAR(MAX).

Snozzlebert0
Related