SQL Server's JSON_VALUE() function is used to extract a scalar value from a JSON string. The function takes two arguments: the JSON string and the JSON path expression that specifies the location of the value to extract.

Syntax

The syntax for the JSON_VALUE() function is as follows:

JSON_VALUE(json_string, path_expression)

Here, json_string is the JSON string that contains the value you want to extract, and path_expression is a string that specifies the location of the value within the JSON object. The path expression can contain dot-separated elements that specify nested objects, array indices for accessing elements of an array, and wildcard characters to match any property or array element.

Example

Here is an example of how to use the JSON_VALUE() function to extract a value from a JSON string:

DECLARE @json NVARCHAR(MAX) = '{"name": "John", "age": 30}'
SELECT JSON_VALUE(@json, '$.name') AS Name

In this example, the JSON_VALUE() function extracts the value of the "name" property from the JSON string and returns it as a scalar value. The result of this query would be "John".

If the specified path expression does not match any value in the JSON string, the JSON_VALUE() function returns NULL.

It is worth noting that JSON_VALUE() function is only available in SQL Server 2016 or later versions. Before SQL Server 2016, there was no built-in support for JSON in SQL Server.