Search code examples
jsonsql-servert-sqlcalculated-columnscheck-constraints

Force check constraints to be evaluated before computed columns

I want to have a JSON column in a table. I want to have (persisted) computed column that extracts useful information from the JSON data. I want to have a "strict" JSON path but I also want to check that the path exists in the JSON so that the error message is specific to the table and isn't just about the illegal JSON path.

CREATE TABLE DataWithJSON (
  DataID BIGINT,
  DataJSON NVARCHAR(MAX) CONSTRAINT CK_DataWithJSON_DataJSON CHECK (
    ISJSON(DataJSON) = 1
    AND JSON_VALUE(DataJSON, 'lax $.Data.ID') IS NOT NULL
  ),
  DataJSONID AS JSON_VALUE(DataJSON, 'strict $.Data.ID') PERSISTED
);

INSERT INTO DataWithJSON (DataID, DataJSON)
  VALUES (666, N'{"Data":{"Name":"Tydýt"}}');

This code returns (on my machine) somewhat mysterious error message

Msg 13608, Level 16, State 2, Line xx Property cannot be found on the specified JSON path.

I would like to see more specific message

Msg 547, Level 16, State 0, Line yy The INSERT statement conflicted with the CHECK constraint "CK_DataWithJSON_DataJSON". The conflict occurred in database "DB", table "schema.DataWithJSON", column 'DataJSON'.

Is this possible to achieve just with table constraints or am I out of luck and do I have to check the JSON in a stored procedure/application before inserting to the table?

One solution would be to have "lax" path in the computed column, which, hopefully, is not the only solution. I will fall back to that solution if there is none other to be found.

Solution

  • You can't control the order that check constraints and computed columns are evaluated but you can use a CASE expression in the computed column definition so that the JSON_VALUE(... 'strict ...) part is only evaluated if the check constraint would pass.

    CREATE TABLE DataWithJSON (
  DataID BIGINT,
  DataJSON NVARCHAR(MAX) CONSTRAINT CK_DataWithJSON_DataJSON CHECK (
    ISJSON(DataJSON) = 1 AND JSON_VALUE(DataJSON, 'lax $.Data.ID') IS NOT NULL
  ),
  DataJSONID AS CASE WHEN ISJSON(DataJSON) = 1 AND JSON_VALUE(DataJSON, 'lax $.Data.ID')  IS NOT NULL THEN JSON_VALUE(DataJSON, 'strict $.Data.ID') END PERSISTED
);

    Msg 547, Level 16, State 0, Line 9 The INSERT statement conflicted with the CHECK constraint "CK_DataWithJSON_DataJSON". The conflict occurred in database "Foo", table "dbo.DataWithJSON", column 'DataJSON'. The statement has been terminated.