Advanced Tips

Consider using check constraints

From SQL 2016, the IsJson function can be used to check that a document is valid JSON (though not that it has all the documents we expect). You might consider adding this as a constraint:

create table Customer
(
    Id nvarchar(200) primary key not null, 
    -- ... other properties...

    [JSON] nvarchar(max) not null 
        constraint CK_Customer_JSON check (IsJson([JSON]) > 0)
)

If you use Compression you can check those too:

[JSONBlob] varbinary(max) not null 
    constraint CK_Customer_JSONBlob 
      check (IsJson(cast(DECOMPRESS([JSONBlob]) as nvarchar(max))) > 0)

The benefit to this is that it will prevent someone adding improperly-formatted JSON, or JSON that the application understands (e.g., UTF-8 encoded) but SQL doesn't.

⚠️ Note that there is a performance penalty to this - for JSON stored as text, it's about 20-50% overhead (like 0.2ms for a small document, and 2ms for a large 65K document). For compressed JSON, it's closer to 100%. But this only affects writes, not reads, so it may be worth it.

Alternatively, if the application isn't busy, you could use ISJSON to do an offline check on a schedule to pick up errors.