SQL Server 2019如何直接从文本文件提取JSON列并解析name属性

针对你的需求，无需SSIS，直接通过TSQL即可实现提取JSON列的name属性值，共有两种可行方案：

方案1：直接读取分隔符文本提取（推荐，SQL Server 2019原生支持） #

该方案通过OPENROWSET指定分隔符直接解析文本的每一列，拿到JSON列后直接提取属性即可，步骤如下：

1. 先开启Ad Hoc分布式查询配置（仅需执行一次）：

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

2. 执行查询直接提取name值：

SELECT 
    JSON_VALUE(json_col, '$.name') AS author_name
FROM OPENROWSET(
    BULK N'替换为你的文本文件绝对路径，例如C:\data\author_list.txt',
    FORMAT = 'CSV',
    FIELDTERMINATOR = '\t', -- 指定制表符为列分隔符
    ROWTERMINATOR = '\n', -- 指定换行符为行分隔符
    CODEPAGE = '65001' -- 文件为UTF8编码用65001，GBK编码替换为936
) AS t(
    -- 按实际列数定义列名和类型，最后一列为JSON列
    col1 NVARCHAR(100),
    col2 NVARCHAR(200),
    col3 INT,
    update_time DATETIME2,
    json_col NVARCHAR(MAX)
)
-- 过滤非JSON格式的脏数据，避免查询报错
WHERE ISJSON(json_col) = 1;

方案2：整行读取后拆分列（兼容特殊格式场景） #

如果文本存在不规则换行、列数不固定等问题，可以先读取整行内容再拆分出JSON列：

WITH raw_file AS (
    -- 读取整个文本文件为单个字符串
    SELECT BulkColumn AS full_content
    FROM OPENROWSET(
        BULK N'替换为你的文本文件绝对路径',
        SINGLE_CLOB
    ) AS f
),
row_split AS (
    -- 按换行符拆分出每一行数据
    SELECT value AS line_content
    FROM raw_file
    CROSS APPLY STRING_SPLIT(full_content, CHAR(10))
    WHERE TRIM(value) <> '' -- 过滤空行
),
json_extract AS (
    -- 提取第5个制表符后的内容作为JSON列，嵌套CHARINDEX的次数=JSON列的序号
    SELECT 
        SUBSTRING(
            line_content,
            CHARINDEX(CHAR(9), line_content, 
                CHARINDEX(CHAR(9), line_content, 
                    CHARINDEX(CHAR(9), line_content, 
                        CHARINDEX(CHAR(9), line_content)+1
                    )+1
                )+1
            )+1,
            LEN(line_content)
        ) AS json_col
    FROM row_split
)
SELECT JSON_VALUE(json_col, '$.name') AS author_name
FROM json_extract
WHERE ISJSON(json_col) = 1;

注：CHAR(9)是制表符的ASCII编码，如果你实际的JSON列是第N列，就嵌套N-1次CHARINDEX定位制表符位置即可。

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