Pivoting JSON Data in SQL Server

JSON has become a common format for data exchange, and SQL Server’s built-in JSON support makes it straightforward to work with JSON data directly in your queries. But what happens when you need to pivot JSON data – transforming nested structures or array elements into a columnar format for reporting or analysis?

SQL Server provides functions like OPENJSON(), JSON_VALUE(), and JSON_QUERY() that let you extract and manipulate JSON data. Combined with standard pivoting techniques, you can reshape JSON data into whatever format you need. This can be particularly useful when you’re receiving JSON from APIs, storing semi-structured data, or working with configuration data that doesn’t fit neatly into traditional tables.

Understanding JSON Data in SQL Server

SQL Server can store JSON as text in VARCHAR() or NVARCHAR() columns, which has been the standard approach for years. SQL Server 2025 introduced a dedicated JSON data type that provides additional validation and performance benefits, though the same JSON functions work with both storage approaches.

The database doesn’t enforce a JSON schema when using text columns. It’s just text that happens to be formatted as JSON. However, the JSON data type validates that the content is well-formed JSON when inserted or updated. Regardless of which storage type you use, you work with SQL Server’s JSON functions to parse and query the data.

The main functions you’ll use are OPENJSON() to parse JSON into rows, JSON_VALUE() to extract scalar values, and JSON_QUERY() to extract objects or arrays. Once you’ve extracted data from JSON into a relational format, you can apply standard pivoting techniques.

Extracting JSON Values with `JSON_VALUE()`

Let’s do a simple example first, where we extract the JSON values with the JSON_VALUE() function. We’ll work with application configuration data stored as JSON. This represents a common scenario where different applications have different configuration settings stored in a flexible JSON format.

We’ll use the following script to create and populate the table:

CREATE TABLE ApplicationConfig (
    ApplicationName VARCHAR(50),
    ConfigData NVARCHAR(MAX)
);

INSERT INTO ApplicationConfig (ApplicationName, ConfigData)
VALUES 
    ('WebAPI', '{"MaxConnections": 100, "TimeoutSeconds": 30, "EnableCaching": true, "CacheExpiryMinutes": 60}'),
    ('BackgroundService', '{"MaxConnections": 50, "TimeoutSeconds": 60, "EnableCaching": false, "RetryAttempts": 3}'),
    ('ReportEngine', '{"MaxConnections": 75, "TimeoutSeconds": 45, "EnableCaching": true, "CacheExpiryMinutes": 120}');

Each application has a JSON object with various configuration settings. We want to pivot this so each setting becomes a column, making it easier to compare configurations across applications.

Now let’s use JSON_VALUE() to extract specific values and pivot them manually with conditional aggregation.

SELECT 
    ApplicationName,
    JSON_VALUE(ConfigData, '$.MaxConnections') AS MaxConnections,
    JSON_VALUE(ConfigData, '$.TimeoutSeconds') AS TimeoutSeconds,
    JSON_VALUE(ConfigData, '$.EnableCaching') AS EnableCaching,
    JSON_VALUE(ConfigData, '$.CacheExpiryMinutes') AS CacheExpiryMinutes
FROM ApplicationConfig
ORDER BY ApplicationName;

Result:

ApplicationName    MaxConnections  TimeoutSeconds  EnableCaching  CacheExpiryMinutes
-----------------  --------------  --------------  -------------  ------------------
BackgroundService  50              60              false          null              
ReportEngine       75              45              true           120               
WebAPI             100             30              true           60

This isn’t really pivoting in the traditional sense. You’re just extracting JSON properties into columns. But it does form the foundation for more complex pivoting operations. JSON_VALUE() takes a JSON path expression (starting with $) and returns the scalar value at that path.

I should point out that JSON_VALUE() returns everything as text by default. If you need proper data types, you have a couple of options, depending on your SQL Server version.

SQL Server 2025 introduced the RETURNING clause, which enables you to specify the data type directly:

SELECT 
    ApplicationName,
    JSON_VALUE(ConfigData, '$.MaxConnections' RETURNING INT) AS MaxConnections,
    JSON_VALUE(ConfigData, '$.TimeoutSeconds' RETURNING INT) AS TimeoutSeconds,
    JSON_VALUE(ConfigData, '$.EnableCaching' RETURNING BIT) AS EnableCaching,
    JSON_VALUE(ConfigData, '$.CacheExpiryMinutes' RETURNING INT) AS CacheExpiryMinutes
FROM ApplicationConfig
ORDER BY ApplicationName;

For earlier SQL Server versions, you can wrap the JSON_VALUE() calls in CAST():

SELECT 
    ApplicationName,
    CAST(JSON_VALUE(ConfigData, '$.MaxConnections') AS INT) AS MaxConnections,
    CAST(JSON_VALUE(ConfigData, '$.TimeoutSeconds') AS INT) AS TimeoutSeconds,
    CAST(JSON_VALUE(ConfigData, '$.EnableCaching') AS BIT) AS EnableCaching,
    CAST(JSON_VALUE(ConfigData, '$.CacheExpiryMinutes') AS INT) AS CacheExpiryMinutes
FROM ApplicationConfig
ORDER BY ApplicationName;

Pivoting JSON Arrays with `OPENJSON()`

When you need to parse JSON arrays into individual rows for querying or pivoting, OPENJSON() is the way to go. It parses JSON and returns a table that you can then pivot using standard techniques.

Example:

CREATE TABLE ServiceMetrics (
    ServiceName VARCHAR(50),
    MetricsData NVARCHAR(MAX)
);

INSERT INTO ServiceMetrics (ServiceName, MetricsData)
VALUES 
    ('Authentication', '[
        {"MetricName": "ResponseTime", "Value": 145},
        {"MetricName": "ErrorRate", "Value": 2},
        {"MetricName": "Throughput", "Value": 1200}
    ]'),
    ('Database', '[
        {"MetricName": "ResponseTime", "Value": 85},
        {"MetricName": "ErrorRate", "Value": 1},
        {"MetricName": "Throughput", "Value": 3500}
    ]'),
    ('Storage', '[
        {"MetricName": "ResponseTime", "Value": 220},
        {"MetricName": "ErrorRate", "Value": 5},
        {"MetricName": "Throughput", "Value": 800}
    ]');

-- First, parse the JSON arrays into rows
SELECT 
    ServiceName,
    MetricName,
    [Value]
FROM ServiceMetrics
CROSS APPLY OPENJSON(MetricsData)
WITH (
    MetricName VARCHAR(50) '$.MetricName',
    [Value] INT '$.Value'
);

Initial output:

ServiceName     MetricName    Value
--------------  ------------  -----
Authentication  ResponseTime  145  
Authentication  ErrorRate     2    
Authentication  Throughput    1200 
Database        ResponseTime  85   
Database        ErrorRate     1    
Database        Throughput    3500 
Storage         ResponseTime  220  
Storage         ErrorRate     5    
Storage         Throughput    800

OPENJSON() with the WITH clause parses each JSON array element and extracts the properties into columns. The CROSS APPLY applies this parsing to each row in the ServiceMetrics table. Now you have a normalized table that you can pivot.

Let’s go ahead and add the pivot:

SELECT ServiceName, ResponseTime, ErrorRate, Throughput
FROM (
    SELECT 
        ServiceName,
        MetricName,
        [Value]
    FROM ServiceMetrics
    CROSS APPLY OPENJSON(MetricsData)
    WITH (
        MetricName VARCHAR(50) '$.MetricName',
        [Value] INT '$.Value'
    )
) AS ParsedMetrics
PIVOT (
    MAX([Value])
    FOR MetricName IN ([ResponseTime], [ErrorRate], [Throughput])
) AS PivotTable
ORDER BY ServiceName;

Result:

ServiceName     ResponseTime  ErrorRate  Throughput
--------------  ------------  ---------  ----------
Authentication  145           2          1200      
Database        85            1          3500      
Storage         220           5          800

This gives you one row per service with columns for each metric type. The combination of OPENJSON() to parse JSON into rows and PIVOT to transform those rows into columns can be a useful pattern when working with JSON data.

Handling Dynamic JSON Properties

When you don’t know the property names in advance, you can use OPENJSON() without the WITH clause to get key-value pairs, then pivot those dynamically.

First, the following query parses JSON into key-value pairs:

SELECT 
    ApplicationName,
    [key] AS PropertyName,
    [value] AS PropertyValue
FROM ApplicationConfig
CROSS APPLY OPENJSON(ConfigData);

Output:

ApplicationName    PropertyName        PropertyValue
-----------------  ------------------  -------------
WebAPI             MaxConnections      100          
WebAPI             TimeoutSeconds      30           
WebAPI             EnableCaching       true         
WebAPI             CacheExpiryMinutes  60           
BackgroundService  MaxConnections      50           
BackgroundService  TimeoutSeconds      60           
BackgroundService  EnableCaching       false        
BackgroundService  RetryAttempts       3            
ReportEngine       MaxConnections      75           
ReportEngine       TimeoutSeconds      45           
ReportEngine       EnableCaching       true         
ReportEngine       CacheExpiryMinutes  120

We can see that this returns each JSON property as a separate row with its name and value.

You can now use dynamic SQL to pivot these properties into columns:

DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- Get distinct property names across all applications
SELECT @columns = STRING_AGG(QUOTENAME(PropertyName), ', ')
FROM (
    SELECT DISTINCT [key] AS PropertyName
    FROM ApplicationConfig
    CROSS APPLY OPENJSON(ConfigData)
) AS Properties;

SET @sql = N'
SELECT ApplicationName, ' + @columns + '
FROM (
    SELECT ApplicationName, [key] AS PropertyName, [value] AS PropertyValue
    FROM ApplicationConfig
    CROSS APPLY OPENJSON(ConfigData)
) AS JsonParsed
PIVOT (
    MAX(PropertyValue)
    FOR PropertyName IN (' + @columns + ')
) AS PivotTable
ORDER BY ApplicationName;';

EXEC sp_executesql @sql;

Result:

ApplicationName    CacheExpiryMinutes  EnableCaching  MaxConnections  RetryAttempts  TimeoutSeconds
-----------------  ------------------  -------------  --------------  -------------  --------------
BackgroundService  null                false          50              3              60            
ReportEngine       120                 true           75              null           45            
WebAPI             60                  true           100             null           30

This discovers all unique property names across your JSON data and creates a column for each one. Applications that don’t have a particular property will show NULL in that column.

Working with Nested JSON Objects

When your JSON has nested objects, you need to navigate the hierarchy using JSON path expressions. Let’s look at a more complex structure:

CREATE TABLE ServerConfig (
    ServerName VARCHAR(50),
    Settings NVARCHAR(MAX)
);

INSERT INTO ServerConfig (ServerName, Settings)
VALUES 
    ('WebServer01', '{
        "Network": {"MaxBandwidthMbps": 1000, "Port": 443},
        "Security": {"EnableFirewall": true, "AllowedIPs": 50},
        "Performance": {"MaxThreads": 200, "CacheSize": 512}
    }'),
    ('AppServer01', '{
        "Network": {"MaxBandwidthMbps": 500, "Port": 8080},
        "Security": {"EnableFirewall": true, "AllowedIPs": 25},
        "Performance": {"MaxThreads": 150, "CacheSize": 256}
    }');

SELECT 
    ServerName,
    JSON_VALUE(Settings, '$.Network.MaxBandwidthMbps') AS NetworkBandwidth,
    JSON_VALUE(Settings, '$.Network.Port') AS NetworkPort,
    JSON_VALUE(Settings, '$.Security.EnableFirewall') AS SecurityFirewall,
    JSON_VALUE(Settings, '$.Security.AllowedIPs') AS SecurityAllowedIPs,
    JSON_VALUE(Settings, '$.Performance.MaxThreads') AS PerformanceThreads,
    JSON_VALUE(Settings, '$.Performance.CacheSize') AS PerformanceCache
FROM ServerConfig
ORDER BY ServerName;

Initial output:

ServerName   NetworkBandwidth  NetworkPort  SecurityFirewall  SecurityAllowedIPs  PerformanceThreads  PerformanceCache
-----------  ----------------  -----------  ----------------  ------------------  ------------------  ----------------
AppServer01  500               8080         true              25                  150                 256             
WebServer01  1000              443          true              50                  200                 512

The dot notation in the JSON path (e.g., $.Network.MaxBandwidthMbps) navigates through nested objects. This extracts values from the nested structure into a flat columnar format.

If you want to pivot the categories themselves (Network, Security, Performance) into columns with their full objects, you’d use JSON_QUERY() instead:

SELECT 
    ServerName,
    JSON_QUERY(Settings, '$.Network') AS Network,
    JSON_QUERY(Settings, '$.Security') AS Security,
    JSON_QUERY(Settings, '$.Performance') AS Performance
FROM ServerConfig
ORDER BY ServerName;

Result:

ServerName   Network                                  Security                                    Performance                          
-----------  ---------------------------------------  ------------------------------------------  -------------------------------------
AppServer01  {"MaxBandwidthMbps": 500, "Port": 8080}  {"EnableFirewall": true, "AllowedIPs": 25}  {"MaxThreads": 150, "CacheSize": 256}
WebServer01  {"MaxBandwidthMbps": 1000, "Port": 443}  {"EnableFirewall": true, "AllowedIPs": 50}  {"MaxThreads": 200, "CacheSize": 512}

JSON_QUERY() returns JSON fragments (objects or arrays) rather than scalar values, so you get the entire nested object as JSON text in each column.

Performance Considerations

Parsing JSON is computationally expensive compared to working with native relational data. If you’re frequently pivoting the same JSON data, consider parsing it once into a regular table structure and working with that instead of parsing on every query.

SQL Server 2025 introduced the ability to create indexes directly on JSON data using CREATE JSON INDEX. In earlier versions, or when using VARCHAR/NVARCHAR columns, SQL Server doesn’t index JSON content directly. For better performance in those cases, extract frequently queried JSON properties into computed columns and index those:

ALTER TABLE ApplicationConfig
ADD MaxConnections AS CAST(JSON_VALUE(ConfigData, '$.MaxConnections') AS INT);

CREATE INDEX IX_MaxConnections ON ApplicationConfig(MaxConnections);

Now queries filtering on MaxConnections can use the index instead of parsing JSON for every row.

When to Pivot JSON Data

You won’t always need to pivot your JSON data. You’ll need to make that determination on a case by case basis. That said, here are some common scenarios where you’ll need to pivot your JSON data:

When you’re preparing data for reporting or analysis tools that expect tabular data
When you need to compare values across different JSON documents side by side
When you’re integrating JSON data with relational data in the same query.

But definitely consider whether pivoting is necessary. If you’re just passing JSON to a client application or another service, leaving it in JSON format might be more efficient. Many modern tools can work with JSON natively without requiring a pivot into columnar format.

Also consider the schema flexibility that JSON provides. One advantage of JSON is handling varying structures without schema changes. Pivoting assumes a consistent structure, so if your JSON documents have widely varying properties, you might lose information or end up with mostly NULL columns.

Quick Summary

Pivoting JSON data in SQL Server combines the flexibility of semi-structured data with the power of relational operations. Using OPENJSON() to parse JSON into rows and then applying standard PIVOT techniques gives you the best of both worlds – you can store flexible JSON data while still being able to analyze and report on it using familiar SQL patterns. The trick is knowing when to extract and pivot versus when to leave data in its original JSON format.

Understanding JSON Data in SQL Server

Extracting JSON Values with JSON_VALUE()

Pivoting JSON Arrays with OPENJSON()

Handling Dynamic JSON Properties

Working with Nested JSON Objects

Performance Considerations

When to Pivot JSON Data

Quick Summary

Extracting JSON Values with `JSON_VALUE()`

Pivoting JSON Arrays with `OPENJSON()`