Querying JSON in SQL Server

by

In SQL Server 2016, Microsoft added support for handling JSON data:

  • You can insert JSON into a table.
  • You can query a table containing JSON (which is what I’ll be talking about in this article).
  • You can modify JSON in a table.

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 to JSON and save it in the database, do basic queries against it. And finally select the JSON string and deserialize it to an object 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()

You can extract a single value from the column containing JSON by using JSON_VALUE(). Here’s an example. 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 HampCode language: plaintext (plaintext)

JSON_VALUE(columnName, propertyName) returns a single value.

Get a JSON object with JSON_QUERY()

To get a JSON object from the column (instead of a single value), use JSON_QUERY(). Here’s an example. 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(columnName, jsonPath) returns a JSON object.

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

You can use JSON_VALUE() in the WHERE clause to filter results by values in JSON string. Here’s an example. 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 LionsCode 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 LionsCode language: plaintext (plaintext)

Note: My sample data only has the Detroit Lions (from the 2020 season), 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 by querying the computed column: “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 LionsCode language: plaintext (plaintext)

See how that simplifies the query quite a bit?

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.

Related Articles