SQL Server 2016 Developer's Guide

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