Extracts a scalar value from a JSON string.


ISJSON ( expression , path )



An expression. Typically the name of a variable or a column that contains JSON text.

If JSON_VALUE finds JSON that is not valid in expression before it finds the value identified by path, the function returns an error. If JSON_VALUE doesn’t find the value identified by path, it scans the entire text and returns an error if it finds JSON that is not valid anywhere in expression.


A JSON path that specifies the property to extract.

If the format of path isn’t valid, JSON_VALUE returns an error.

The following list explains the available path elements:

  • The dollar sign ($) represents the context item.

  • The property path is a set of path steps. Path steps can contain the following elements and operators:
    • Key names. For example, $.name and $.”first name”. If the key name starts with a dollar sign or contains special characters such as spaces, surround it with quotes.

    • Array elements. For example, $.product[3]. Arrays are zero-based.

    • The dot operator (.) indicates a member of an object. For example, in $.people[1].surname, surname is a child of people.

Return value#

Returns a single text value of type nvarchar(4000).


The following example extracts the value of the JSON property town into a local variable.


SET @jsonInfo=N'{"info":{"address":[{"town":"Warsaw"},{"town":"Miami"}]}}';

SET @town=JSON_VALUE(@jsonInfo,'$.info.address[0].town'); -- Warsaw
SET @town=JSON_VALUE(@jsonInfo,'$.info.address[1].town'); -- Miami

See Also#