SQL Server 2017：将表中JSON列转换为动态列扁平化表

Hey there! Let's figure out how to flatten that JSON data stored in your SQL Server 2017 table into a dynamic flat table, where the column names are pulled directly from the Name field in the JSON's Attributes array. Here's a step-by-step solution that works for your scenario:

Step 1: Set Up Test Data (Optional) #

First, let's create a sample table and insert your example JSON to test with:

CREATE TABLE TestJSONTable (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    JSONData NVARCHAR(MAX)
);

INSERT INTO TestJSONTable (JSONData)
VALUES ('{"JobID":1,"ItemID":1,"Attributes":[{"ID":1,"Name":"Request Type","Value":"1","ValueString":"Buy"},{"ID":3,"Name":"Country","Value":"2","ValueString":"USA"},{"ID":5,"Name":"Number","Value":"1","ValueString":"1"}]}');

Step 2: Parse the JSON into Row-Level Data #

Before flattening, we need to extract the core fields (like JobID, ItemID) and the key-value pairs from the Attributes array using OPENJSON:

SELECT 
    JSON_VALUE(jd.JSONData, '$.JobID') AS JobID,
    JSON_VALUE(jd.JSONData, '$.ItemID') AS ItemID,
    attr.Name,
    attr.ValueString
FROM TestJSONTable jd
CROSS APPLY OPENJSON(jd.JSONData, '$.Attributes')
WITH (
    Name NVARCHAR(100) '$.Name',
    ValueString NVARCHAR(100) '$.ValueString'
) attr;

This query returns a row for each attribute in the JSON, which looks like this:

Step 3: Dynamic PIVOT to Flatten the Data #

Since the column names (from Name) aren't fixed, we need to use dynamic SQL to build the PIVOT query on the fly:

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

-- Get all unique attribute names to use as column headers
SELECT @cols = STRING_AGG(QUOTENAME(Name), ', ')
FROM (
    SELECT DISTINCT attr.Name
    FROM TestJSONTable jd
    CROSS APPLY OPENJSON(jd.JSONData, '$.Attributes')
    WITH (Name NVARCHAR(100) '$.Name') attr
) AS UniqueNames;

-- Build the dynamic PIVOT query
SET @query = N'
SELECT JobID, ItemID, ' + @cols + N'
FROM (
    SELECT 
        JSON_VALUE(jd.JSONData, ''$.JobID'') AS JobID,
        JSON_VALUE(jd.JSONData, ''$.ItemID'') AS ItemID,
        attr.Name,
        attr.ValueString
    FROM TestJSONTable jd
    CROSS APPLY OPENJSON(jd.JSONData, ''$.Attributes'')
    WITH (
        Name NVARCHAR(100) ''$.Name'',
        ValueString NVARCHAR(100) ''$.ValueString''
    ) attr
) AS SourceData
PIVOT (
    MAX(ValueString)
    FOR Name IN (' + @cols + N')
) AS PivotTable;';

-- Execute the dynamic query
EXEC sp_executesql @query;

Running this gives you the flattened table you want:

Key Notes #

• STRING_AGG: This SQL Server 2017+ function lets us easily concatenate dynamic column names. QUOTENAME ensures attribute names with spaces or special characters are wrapped in square brackets to avoid syntax errors.
• Multiple Records: If your table has multiple JSON entries, this query automatically includes all unique attribute names across all records. Entries missing a specific attribute will show NULL in that column.
• Data Types: We used ValueString for consistency, but if you need to cast values to specific types (like integers for Number), adjust the WITH clause in OPENJSON or add a CASE statement in the source data query—just ensure consistency for each attribute name.

内容的提问来源于stack exchange，提问作者user3933891