Questions tagged [open-json]

OpenJson was introduced in SQL Server 2016.
It takes in a nvarchar containing JSON data, with an optional nvarchar string containing a JSON Path Expression, and an optional With clause.

When the With clause is not supplied, OpenJson will return a table containing three columns:
Key (nvarchar(4000)), Value(nvarchar(max)) and Type (int).
values of the Type columns indicates the following:

Type    JSON data type
0       null
1       string
2       int
3       true/false
4       array
5       object

The With clause allows the user to explicitly define the columns of the table returned.
The With clause contains three parameters for each value to get from the JSON:
Column name, data type, and an optional JSON Path expression (if not supplied, OPENJSON will try to get a property with the same name as the target column.

If the json property extracted contains an inner json object, the AS JSON option can be specified with this column, to enable further JSON processing.

Articles

• SQL Server JSON functions: a bridge between NoSQL and relational worlds