Built-in JSON support in SQL Server 2016

- New XML is JSON.
- Conceptually similar to XML support.
- FOR JSON clause.

SELECT *
FROM dbo.MyTable
FOR JSON AUTO

- New function ISJSON(): validates for well-formed JSON string. Can be used as CHECK CONSTRAINT.
- OPENJSON: transforms JSON text to table
SELECT
    PersonId = JSON_VALUE(@personJsonAsString, '$.Id'),
    PersonName = JSON_VALUE(c.value, '$.Name')
FROM
    OPENJSON(@personJsonAsString, '$.Contacts') as c

- JSON_QUERY: queries by path expression and returns a nested array
- JSON_VALUE: queries by path expression and returns a scalar value
SELECT *
FROM dbo.MyTable
WHERE JSON_VALUE(MyColumn, '$.myProperty') = 'MyValue'

Limitations
- No native "json" data type => use nvarchar(max).
- No custom JSON index => create computed columns over desired properties, and then index the computed columns.
- No JSON "DML": cannot directly modify JSON content. Use string functions for manipulation.

Is this useful?