If I'm reading this correctly, the unfortunate part about using nvarchar rather than a native JSON data type is that it's entirely possible to store malformed JSON into the database, thus putting the responsibility for validation on your business logic. When querying, you need to exclude malformed JSON by using the ISJSON() operator.

I get the argument that nvarchar makes it work with all tooling, but one could make the argument that if your tools don't support JSON already then perhaps they need to get with the times.

Imagine if SQL Server didn't have any date types and you had to query an nvarchar data type with ISDATETIME() and then get the year via DATE_VALUE(t.OrderDate, '$.year'). Seems a bit too hackish.