JSON_VALUE

JSON_VALUE#

Extracts a scalar value from a JSON string.

Syntax#

ISJSON ( expression , path )

Arguments#

expression

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.

path

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

Examples#

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

DECLARE @jsonInfo NVARCHAR(MAX)
DECLARE @town NVARCHAR(32)

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#