Querying JSON in SQL Server

In SQL Server 2016 they added support for handling JSON data. You can add JSON data, query it, and modify it.

The JSON API in SQL Server is well-suited for simple scenarios where you only need to do basic querying and manipulation. For advanced querying, the SQL Server JSON API gets complicated and hard to use, so you may want to look into other options. If you want a full-blown document-based database, you should use a NoSQL database instead (even Microsoft recommends this!).

An example of a simple scenario is if you want to serialize an object and save it in the database, do basic queries against it, and mainly get it later and deserialize it in your code.

In this article, I’ll show how to use the SQL Server JSON API to do basic queries against JSON, and how to add computed columns to simplify the queries for the client.

First – Add JSON data to a table

JSON data is just string data. You can store it in a nvarchar column (specify a size or use MAX). I have the following table definition:

CREATE TABLE dbo.[NFLTeams]( [TeamName] [nvarchar](50) NOT NULL, [People] [nvarchar](max) NOT NULL )
Code language: SQL (Structured Query Language) (sql)

I have the following JSON:

{ "Players": { "QB1": { "Name": "Matthew Stafford", "YearsOfExperience": 12, "College": "Georgia" }, "RB1": { "Name": "D'Andre Swift", "YearsOfExperience": 1, "College": "Georgia" }, "WR1": { "Name": "Kenny Golladay", "YearsOfExperience": 4, "College": "Northern Illinois" }, "TE1": { "Name": "T.J. Hockenson", "YearsOfExperience": 2, "College": "Iowa" } }, "GeneralManager": null, "Coaches": { "HeadCoach": { "Name": "Darrell Bevell", "Notes": "Promoted from OC to interim HC when Lions fired Matt Patricia" }, "OffensiveCoordinator": null, "DefensiveCoordinator": { "Name": "Cory Undlin", "Notes": null }, "SpecialTeamsCoordinator": { "Name": "Brayden Coombs", "Notes": null } }, "Owner": { "Name": "Sheila Ford Hamp" } }
Code language: JSON / JSON with Comments (json)

The JSON data is not special. It’s just string data. To insert it into a table, pass in the JSON string parameter just like you would any other string parameter, like this:

INSERT INTO [NFLTeams] ([TeamName] ,[People]) VALUES (@teamName ,@peopleJson)
Code language: SQL (Structured Query Language) (sql)

Get a single value with JSON_VALUE()

This query answers the question: “Who is the owner of the Detroit Lions?”

SELECT JSON_VALUE(People, '$.Owner.Name') FROM NFLTeams WHERE TeamName = 'Detroit Lions'
Code language: SQL (Structured Query Language) (sql)

This returns the following value:

Sheila Ford Hamp
Code language: plaintext (plaintext)

JSON_VALUE(ColumnWithJSON, PathToJSONPropertyYouWant) returns a single value.

Get a JSON object with JSON_QUERY()

This query answers the question: “Who plays for the Detroit Lions?”

SELECT JSON_QUERY(People, '$.Players') FROM NFLTeams WHERE TeamName = 'Detroit Lions'
Code language: SQL (Structured Query Language) (sql)

This returns the Players JSON object:

{ "QB1": { "Name": "Matthew Stafford", "YearsOfExperience": 12, "College": "Georgia" }, "RB1": { "Name": "D'Andre Swift", "YearsOfExperience": 1, "College": "Georgia" }, "WR1": { "Name": "Kenny Golladay", "YearsOfExperience": 4, "College": "Northern Illinois" }, "TE1": { "Name": "T.J. Hockenson", "YearsOfExperience": 2, "College": "Iowa" } }
Code language: JSON / JSON with Comments (json)

JSON_QUERY(ColumnWithJSON, PathToJSONObjectYouWant) returns a JSON object.

Filter rows based on values in the JSON – JSON_VALUE() in the WHERE clause

This query answers the question: “Which team has Matthew Stafford as the starting quarterback?”

SELECT TeamName FROM NFLTeams WHERE JSON_VALUE(People, '$.Players.QB1.Name') = 'Matthew Stafford'
Code language: SQL (Structured Query Language) (sql)

This returns the following:

Detroit Lions
Code language: plaintext (plaintext)

As you can see, you can use JSON_VALUE() in the WHERE clause.

Here’s another example. This query answers the question: “Which team doesn’t have a general manager right now?”

SELECT TeamName FROM NFLTeams WHERE JSON_VALUE(People, '$.GeneralManager') IS NULL
Code language: SQL (Structured Query Language) (sql)

This returns the following:

Detroit Lions
Code language: plaintext (plaintext)

Note: My sample data only has the Detroit Lions, so it only returns them in this query. At the time of this writing, several teams don’t have General Managers (due to firing them in the middle of the season for poor performance).

Add a computed column so the client code doesn’t have to call JSON_VALUE()

If you want to simplify your queries, you can add a computed column that extracts out JSON values into a separate column.

Add the computed column like this:

ALTER TABLE [NFLTeams] ADD QB1 AS JSON_VALUE(People,'$.Players.QB1.Name') PERSISTED
Code language: SQL (Structured Query Language) (sql)

Now let’s answer this question again: “Which team has Matthew Stafford as the starting quarterback?”

SELECT TeamName FROM NFLTeams WHERE QB1 = 'Matthew Stafford'
Code language: SQL (Structured Query Language) (sql)

This returns the following:

Detroit Lions
Code language: plaintext (plaintext)

Add an index on the computed column

Besides making your queries simpler, adding a PERSISTED computed column also makes it so you can include this column in an index, which can improve performance.

The following adds an index on the QB1 computed column:

CREATE UNIQUE NONCLUSTERED INDEX [QB1Index] ON [dbo].[NFLTeams] ( [QB1] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Code language: SQL (Structured Query Language) (sql)

Now when I execute the SELECT query, according to the execution plan, it’s doing an index lookup instead of a table scan.

You’ll have to decide if adding this column to an index makes sense in your situation.

Leave a Comment