Modern applications often exchange information in JSON, and that data often ends up in SQL Server. While JSON’s flexible structure makes it ideal for storing dynamic or nested data, it doesn’t fit neatly into traditional relational tables. The good news is that SQL Server includes a good selection of JSON functions that let you parse, query, and transform JSON content into structured rows and columns. This means that you can work with your JSON data just like any other table.
A Quick Look at JSON Support in SQL Server
SQL Server introduced native JSON support starting with SQL Server 2016. And, although there’s now a dedicated JSON data type (introduced in SQL Server 2025), this doesn’t help if you’re not using that version. But all is not lost. You can store JSON text in an NVARCHAR column or variable.
Here are some of the JSON functions we can use when we need to query data from JSON documents:
OPENJSON(): Converts JSON text into rows.JSON_VALUE(): Extracts a single scalar value.JSON_QUERY(): Extracts a JSON object or array.ISJSON(): Checks if a string is valid JSON.
Example
Imagine you’re storing responses from a customer satisfaction survey. Each submission includes metadata about the respondent and a set of answers to several questions.
Here’s a sample JSON dataset stored in a variable that we can use for our example:
DECLARE @survey NVARCHAR(MAX) = '
[
{
"response_id": 101,
"submitted_at": "2025-10-01T14:23:00",
"respondent": {
"name": "Amara Chen",
"email": "[email protected]",
"age": 29
},
"answers": [
{"question": "Ease of Use", "score": 4},
{"question": "Design", "score": 5},
{"question": "Support", "score": 3}
]
},
{
"response_id": 102,
"submitted_at": "2025-10-02T09:17:00",
"respondent": {
"name": "Diego Ramos",
"email": "[email protected]",
"age": 34
},
"answers": [
{"question": "Ease of Use", "score": 5},
{"question": "Design", "score": 4},
{"question": "Support", "score": 4}
]
}
]
';Reading the Top-level Elements
If you just need to get the basic details for each survey response, OPENJSON() can parse the array into one row per item. Nested objects such as respondent and answers can be kept intact by marking them as AS JSON in the WITH clause:
SELECT
response_id,
submitted_at,
respondent,
answers
FROM OPENJSON(@survey)
WITH (
response_id INT '$.response_id',
submitted_at DATETIME2 '$.submitted_at',
respondent NVARCHAR(MAX) '$.respondent' AS JSON,
answers NVARCHAR(MAX) '$.answers' AS JSON
);Result:
response_id submitted_at respondent answers
----------- ------------------------ ------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------
101 2025-10-01T14:23:00.000Z { "name": "Amara Chen", "email": "[email protected]", "age": 29 } [ {"question": "Ease of Use", "score": 4}, {"question": "Design", "score": 5}, {"question": "Support", "score": 3} ]
102 2025-10-02T09:17:00.000Z { "name": "Diego Ramos", "email": "[email protected]", "age": 34 } [ {"question": "Ease of Use", "score": 5}, {"question": "Design", "score": 4}, {"question": "Support", "score": 4} ]
2 row(s) returned
That returns one row per survey response, with the respondent and answers fields still in JSON form. The AS JSON keyword is needed here. Without it, SQL Server would return NULL for those columns because it can’t coerce nested objects or arrays into scalar values.
Pulling Out Nested Object Values
If you’d like to include some respondent details as actual columns, you can use JSON_VALUE() to extract specific fields from the nested respondent object:
SELECT
response_id,
submitted_at,
JSON_VALUE(respondent, '$.name') AS respondent_name,
JSON_VALUE(respondent, '$.email') AS respondent_email,
JSON_VALUE(respondent, '$.age') AS respondent_age
FROM OPENJSON(@survey)
WITH (
response_id INT '$.response_id',
submitted_at DATETIME2 '$.submitted_at',
respondent NVARCHAR(MAX) '$.respondent' AS JSON
);Result:
response_id submitted_at respondent_name respondent_email respondent_age
----------- ------------------------ --------------- ----------------------- --------------
101 2025-10-01T14:23:00.000Z Amara Chen [email protected] 29
102 2025-10-02T09:17:00.000Z Diego Ramos [email protected] 34
2 row(s) returned
This gives a flatter result that’s easy to query or join, while still keeping the answers array available if you need it later.
Expanding Arrays into Rows
To analyze answers individually, you can use a CROSS APPLY with another OPENJSON to unroll the answers array into separate rows:
SELECT
s.response_id,
JSON_VALUE(s.respondent, '$.name') AS respondent_name,
JSON_VALUE(s.respondent, '$.email') AS respondent_email,
JSON_VALUE(s.respondent, '$.age') AS respondent_age,
a.question,
a.score
FROM OPENJSON(@survey)
WITH (
response_id INT '$.response_id',
respondent NVARCHAR(MAX) '$.respondent' AS JSON,
answers NVARCHAR(MAX) '$.answers' AS JSON
) AS s
CROSS APPLY OPENJSON(s.answers)
WITH (
question NVARCHAR(200) '$.question',
score INT '$.score'
) AS a;Result:
response_id respondent_name respondent_email respondent_age question score
----------- --------------- ----------------------- -------------- ----------- -----
101 Amara Chen [email protected] 29 Ease of Use 4
101 Amara Chen [email protected] 29 Design 5
101 Amara Chen [email protected] 29 Support 3
102 Diego Ramos [email protected] 34 Ease of Use 5
102 Diego Ramos [email protected] 34 Design 4
102 Diego Ramos [email protected] 34 Support 4
Here, each respondent’s answers appear on separate rows. This makes it easy to do things like calculate averages, build reports, or simply visualize the results.
A Note on the New JSON Type
As mentioned, SQL Server now supports a json data type (currently in preview). It stores JSON data in a binary-optimized format and offers validation and performance benefits. However, many of the existing JSON functions (including OPENJSON(), JSON_VALUE(), and JSON_QUERY()) still expect NVARCHAR input in most scenarios (depending on the platform). Until those functions fully support the new type across all platforms, you can safely continue using NVARCHAR(MAX) and cast to or from json when needed.