Microsoft SQL Server JSON in Sql Server
Syntax
- JSON_VALUE(expression , path) -- extract a scalar value from a JSON string.
- JSON_QUERY( expression [ , path ] ) -- Extracts an object or an array from a JSON string.
- OPENJSON( jsonExpression [ , path ] ) -- table-value function that parses JSON text and returns objects and properties in JSON as rows and columns.
- ISJSON( expression ) -- Tests whether a string contains valid JSON.
- JSON_MODIFY( expression , path , newValue ) -- Updates the value of a property in a JSON string and returns the updated JSON string.
Parameters
| Parameters | Details |
|---|---|
| expression | Typically the name of a variable or a column that contains JSON text. |
| path | A JSON path expression that specifies the property to update. path has the following syntax: [append] [ lax | strict ] $.<json path> |
| jsonExpression | Is a Unicode character expression containing the JSON text. |
Remarks
The OPENJSON function is only available under compatibility level 130. If your database compatibility level is lower than 130, SQL Server will not be able to find and execute OPENJSON function. Currently all Azure SQL databases are set to 120 by default. You can change the compatibility level of a database using the following command:
ALTER DATABASE <Database-Name-Here> SET COMPATIBILITY_LEVEL = 130
