OpenJson is a T-SQL (SQL Server) built in table valued function, introduced in 2016 version. Use this tag with questions related to this command.
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.
