JSON in Sql Server

Download sql-server eBook

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

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

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

Related Examples

Stats

148 Contributors: 2
Friday, July 29, 2016
Licensed under: CC-BY-SA

Not affiliated with Stack Overflow
Rip Tutorial: info@zzzprojects.com

Download eBook