- 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.