How to Query JSON in SQL Server

In this article, we will walk through querying JSON data stored in SQL Server, simplifying queries using computed columns, and improving performance through indexing.

Key Capabilities of SQL Server JSON API

SQL Server’s JSON capabilities are designed for basic use cases, such as:

• Inserting JSON into a table
• Querying JSON data from a table
• Modifying JSON content in a table

However, for more complex or advanced querying needs, the built-in JSON API can become cumbersome, and in such cases, exploring other alternatives, like NoSQL databases, may be preferable.

Let's look at a practical example, where we serialize and save a JSON object to a table, perform simple queries on it, and finally deserialize it back into an object.

1. Add JSON Data to a Table

The JSON data is stored as a string in an nvarchar column, which can be created as follows:

CREATE TABLE dbo.[Teams](
	  NOT NULL,
	[Details] [nvarchar](max) NOT NULL
)

Here’s a sample JSON object representing a football team’s roster:

{
  "Players": {
    "QB1": {
      "Name": "Aaron Rodgers",
      "YearsOfExperience": 17,
      "College": "California"
    },
    "RB1": {
      "Name": "Aaron Jones",
      "YearsOfExperience": 5,
      "College": "UTEP"
    }
  },
  "GeneralManager": "Brian Gutekunst",
  "Owner": {
    "Name": "Mark Murphy"
  }
}

Inserting this JSON into the table:

INSERT INTO [Teams] ([TeamName], [Details])
VALUES ('Green Bay Packers', @teamJson)

2. Extracting Single Values with JSON_VALUE()

To query a specific value from the JSON, use the JSON_VALUE() function. For example, to find out the owner of the "Green Bay Packers":

SELECT JSON_VALUE(Details, '$.Owner.Name')
FROM Teams
WHERE TeamName = 'Green Bay Packers'

This returns:

Mark Murphy

3. Extracting JSON Objects with JSON_QUERY()

To extract an entire JSON object, use the JSON_QUERY() function.

For example, let’s get the list of players:

SELECT JSON_QUERY(Details, '$.Players')
FROM Teams
WHERE TeamName = 'Green Bay Packers'

This returns the "Players" object:

{
  "QB1": {
    "Name": "Aaron Rodgers",
    "YearsOfExperience": 17,
    "College": "California"
  },
  "RB1": {
    "Name": "Aaron Jones",
    "YearsOfExperience": 5,
    "College": "UTEP"
  }
}

4. Filtering Rows Using JSON Values

You can filter rows based on the values inside the JSON.

For example, to find out which team has Aaron Rodgers as the starting quarterback:

SELECT TeamName
FROM Teams
WHERE JSON_VALUE(Details, '$.Players.QB1.Name') = 'Aaron Rodgers'

This returns:

Green Bay Packers

5. Using Computed Columns to Simplify Queries

To simplify querying JSON values, you can create a computed column that extracts a specific value from the JSON.

For instance, to directly retrieve the quarterback’s name:

ALTER TABLE [Teams] ADD QB1 AS JSON_VALUE(Details, '$.Players.QB1.Name') PERSISTED

Now, querying for the team with Aaron Rodgers as QB is simpler:

SELECT TeamName FROM Teams
WHERE QB1 = 'Aaron Rodgers'

This returns:

Green Bay Packers

6. Indexing Computed Columns

You can also index computed columns to improve query performance. Adding an index on the QB1 column:

CREATE UNIQUE NONCLUSTERED INDEX [QB1Index] ON [dbo].[Teams]([QB1] ASC)

By doing this, queries like the one above will now benefit from faster index lookups rather than a full table scan.

SQL Server’s JSON support enables users to store, query, and manipulate JSON data efficiently. While it is perfect for straightforward scenarios, more complex or document-centric data might require a NoSQL database. By following the steps outlined above, you can optimize your SQL Server setup for JSON-based data, enhancing both performance and query simplicity.