Microsoft SQL Server JSON in Sql Server


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


expressionTypically the name of a variable or a column that contains JSON text.
pathA JSON path expression that specifies the property to update. path has the following syntax: [append] [ lax | strict ] $.<json path>
jsonExpressionIs a Unicode character expression containing the JSON text.


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: