Rajendra Gupta

Extract scalar values from JSON data using JSON_VALUE()

May 5, 2020 by

In this article, we will explore JSON_VALUE() function in SQL Server to extract scalar values from JSON data.

Introduction to JSON

JSON is an acronym for “JavaScript Object Notation”. It is a lightweight data exchange language. If you are familiar with XML, it is a bit hard to understand. You can easily interpret JSON formatted data using its key-value pair.

JSON is a popular language and many NoSQL databases such a Couchbase, AWS DynamoDB. It is popular in modern web and mobile applications.

SQL Server 2016 and later supports JSON format data. You should know JSON format and work with JSON data in SQL Server. It also creates a bridge between a relational database and NoSQL systems.

SQL Server provides various built-in-functions to parse, read, transform, and convert JSON objects. You can also format the T-SQL results in the JSON format. You can go through article category k in SQLShack to understand more details about it.

You can refer to the following image from Microsoft docs to understand the interoperability between SQL table and JSON.

We have the following JSON functions in SQL Server:

  • ISJSON(): It checks whether we have a valid JSON or not
  • JSON_VALUE(): We can extract a scalar value from the JSON string. We will explore this function in detail in this article
  • JSON_QUERY: We can extract an array or string from the JSON_QUERY() output
  • JSON_MODIFY(): We can modify a value in the JSON Data using this JSON-MODIFY() function

Overview of built-in JSON support

Syntax of JSON_VALUE()

JSON_VALUE ( expression ,[Path Mode] JSON_path )

  • Expression: It is a variable or column containing a JSON string. It should be a valid expression, and else it returns an error
  • JSON_Path: It is the location of a scalar value in the JSON string
  • Path mode: It is an optional argument. We can specify the lax or strict value in this mode. It uses LAX as a default path mode. We will understand it using examples

Let’s understand the JSON_VALUE() function using various examples.

Example 1: Search a key value from JSON string

In the query below, we defined a JSON expression and path.

  • It has a JSON expression defined as a key (Name) and its value (“Rajendra”)

  • It specifies $.Name argument as JSON path. This path should reference the key in the JSON expression

    Search a key value from JSON string

We cannot use a space character in the JSON key. For example, if we run the following code for a key (First Name) and want to retrieve a value for it, it gives us inappropriate format error.

JSON path error

In the below query, the JSON path does not contain an argument value that does not refer to the JSON string. Here, the JSON string does not have the first name key, therefore so we get NULL value in the output using default path mode.

Default lax path mode

Example 2: Default LAX path mode in JSON Data

As we specified earlier, JSON_VALUE() function uses LAX as the default mode. We can specify it explicitly as well, and it returns a similar output as shown below.

Default LAX path mode in JSON Data

Example 3: Strict path mode in JSON

Let’s change the mode to Strict, and it gives you an error message in case the key does not exist.

Strict path mode in JSON

Now, let’s change the correct key from the JSON string, and we get output in the Strict path.

Modify JSON

You should take a note that it is a case sensitive operation; if we specify the path mode to Strict, it gives the following error.

case sensitive operation

Similarly, you get an error if you use a capital letter in LAX mode, it also gives the error message.

capital letter in LAX mode

Example 4: Use an Array in JSON data with JSON_VALUE() function

We can use an array to store multiple values. It uses square brackets ([]) to define the array. In the below code, we have an array for Employees records and puts JSON into a variable called @data.

The first record in JSON is always index as zero. For example, in the array, we get the first record by specifying index position zero.

It returns the output of the first employee data.

Use an Array in JSON data with JSON_VALUE() function

We can use SQL UNION ALL operator to retrieve both records and display them in a tabular format.

We get the following output from the JSON_VALUE() function in an array.

Use an Array in JSON with UNION ALL

Similarly, look at the following example. It holds an array for favorite subjects of a student in the info JSON key. We require to retrieve student name and his second favorite subject listed in an array.

We can use the following code with the JSON_Value function.

  • Retrieve the student name using the JSON_VALUE function, as shown below. We specify the JSON key (info) and specify the item name you need the data

  • For the second favorite subject, we specify the array element position. As you know, in an array first item starts with zero index and we use [1] for the second element

It gives you the desired output, as shown below.

JSON_VALUE output

As specified earlier, we can get a scalar value from JSON data using the JSON_VALUE() function. In the above example, if we try to retrieve a complete array, it returns a NULL value in the output using the default lax path mode.

NULL value in the array output

We do not want NULL value in the output. It gives an assumption that the specified key does not exist in JSON expression. We should use a strict path mode to get the exact error message.

You get an error in the strict mode because it could not retrieve the array from the JSON data.

Scalar value error in strict mode

Example 5: Retrieve value from a nested JSON key data

In the below code, we have an address key that contains further nested elements. For example, address contains values for different keys FlatNo, Locality, and City.

Now, we want to retrieve a specific subkey from the Address JSON key. For example, we require City from the Address key. For this requirement, we need to specify the key order in the following format.

  • $.Employee[0] refers to the first record in the array

  • The address is the first key in Employees, and City is the second key, so the complete expression is Employees[0].Address.City

It fetches the information from the JSON data and gives you an appropriate output.

Nested JSON key data

Conclusion

In this article, we explored the useful JSON_VALUE() function to retrieve a scalar value from the JSON data using various examples. It also demonstrated the difference in lax and strict path modes available with this function. You should explore JSON and process it to handle it with SQL Server.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. SQL Server JSON functions: a bridge between NoSQL and relational worlds
  2. Static Data Masking in SSMS 18
  3. Native JSON Support in SQL Server 2016
  4. How to import/export JSON data using SQL Server 2016
  5. The JSON_QUERY() function to extract objects from JSON Data
168 Views