Microsoft SQL Server JSON in Sql Server

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

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


Got any Microsoft SQL Server Question?