Extracting values from a JSON text

As mentioned earlier in this chapter, JSON has four primitive types (string, number, Boolean, and null) and two complex (structure) types: object and array. SQL Server 2016 offers two functions to extract values from a JSON text:

• JSON_VALUE: This is used to extract values of primitive data types

• JSON_QUERY: This is used to extract a JSON fragment or to get a complex value (object or array)

JSON_VALUE

The JSON_VALUE function extracts a scalar value from a JSON string. It accepts two input arguments:

• Expression: This is  JSON text in the Unicode format.

• Path: This is an optional argument. It is a JSON path expression and you can use it to specify a fragment of the input expression.

The return type of the function is nvarchar(4000), with the same collation as in the input expression. If the extracted value is longer than 4,000 characters, the function returns NULL provided the path is in lax mode or an error message in the case of strict mode.

If either the expression...