SQL Server中OPENJSON仅返回5000行？求批量处理方案

OPENJSON是否有5000行返回上限？ #

OPENJSON本身没有5000行的返回限制，它可以解析任意大小的合法JSON数组（只要JSON数据完整且SQL Server内存足够）。你遇到的问题大概率是数据加载环节的截断，或者查询执行/显示的隐性限制导致的。

问题排查步骤 #

1. 验证JSON数据完整性
   检查rawJSONupload表中目标文件的JSONData字段是否完整：

   SELECT DATALENGTH(JSONData) AS JSONLength, filename 
   FROM rawJSONupload 
   WHERE filename = 'filename'
   

   将返回的JSONLength与Blob存储中原始JSON文件的大小对比（注意nvarchar(max)的字节数是字符数×2），如果长度差距很大，说明ADF加载时JSON数据被截断了。

2. 确认实际插入行数
   检查jsonTable中实际插入的行数，而非仅看查询工具的显示结果：

   SELECT COUNT(*) FROM jsonTable
   

   部分查询工具（如SSMS）默认会限制结果集的显示行数，但实际插入的行数可能是完整的。

突破限制的解决方法 #

1. 修复JSON数据截断问题 #

如果确认是ADF加载时截断了JSON：

• 确保rawJSONupload表的JSONData字段类型为nvarchar(max)，不要设置固定长度。
• 在ADF复制活动的映射设置中，将JSONData字段的类型映射为String且长度设为max，避免固定长度限制导致截断。

2. 修改OPENJSON查询写法 #

将子查询改为CROSS APPLY的写法，避免子查询可能带来的隐性问题，同时更适配批量解析场景：

INSERT INTO jsonTable
SELECT j.*
FROM rawJSONupload r
CROSS APPLY OPENJSON(r.JSONData)
WITH (
    field1 nvarchar(5),
    field2 real,
    field3 real,
    EnteredDate datetime,
    FilePath nvarchar(500)
) j
WHERE r.filename = 'filename'

其他批量加载JSON的高效方法 #

1. 直接用ADF复制活动解析加载 #

跳过中间临时表，直接在ADF中完成JSON解析与加载：

• 源数据集选择Azure Blob存储的JSON文件，配置好文件路径。
• 目标数据集选择SQL Server的jsonTable。
• 在复制活动的源设置中，选择JSON格式，设置JSON path为$（解析整个数组）。
• 在映射选项卡中直接对应JSON字段与目标表字段，ADF会自动批量解析并加载所有数据，效率远高于先存临时表再解析。

2. 使用SQL Server的OPENROWSET直接加载Blob文件 #

如果SQL Server可以直接访问Azure Blob存储（通过SAS令牌或托管身份授权），可以直接从Blob加载并解析JSON：

INSERT INTO jsonTable
SELECT *
FROM OPENROWSET(
    BULK 'https://your-storage-account.blob.core.windows.net/your-container/filename.json',
    FORMAT = 'JSON',
    SINGLE_CLOB,
    CREDENTIAL = 'YourBlobCredential' -- 提前创建的Blob存储凭据
) WITH (
    field1 nvarchar(5),
    field2 real,
    field3 real,
    EnteredDate datetime,
    FilePath nvarchar(500)
) AS j

3. 使用SSIS批量加载 #

如果熟悉SQL Server Integration Services，可以创建SSIS包：

• 使用Foreach Loop Container遍历Blob存储的JSON文件。
• 使用JSON Source组件解析文件数据。
• 通过OLE DB Destination组件将数据批量插入目标表。

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