如何在SQL Server 2016中读取ndJSON格式数据

SQL Server 2016并没有专门内置直接读取ndJSON（换行分隔JSON）的函数，但我们可以通过两种简单有效的方法来处理这种格式的数据，下面结合你的示例代码来详细说明：

方法一：将ndJSON转换为标准JSON数组后解析 #

这种方法的核心思路是先读取整个ndJSON文件，然后将其转换为OPENJSON可以直接处理的标准JSON数组格式：

Declare @ndJSON varchar(max)
-- 读取整个ndJSON文件内容
SELECT @ndJSON = BulkColumn FROM OPENROWSET (BULK 'C:\examplepath\filename.ndJSON', SINGLE_CLOB) as j

-- 将ndJSON转换为标准JSON数组：移除换行符，用逗号分隔每个JSON对象，再包裹上数组括号
SET @ndJSON = '[' + REPLACE(REPLACE(@ndJSON, CHAR(13), ''), CHAR(10), ',') + ']'

-- 现在可以用你原来的OPENJSON逻辑解析了
Select * FROM OPENJSON(@ndJSON)
With (
    House varchar(50),
    Car varchar(4000) '$.Attributes.Car',
    Door varchar(4000) '$.Attributes.Door',
    Bathroom varchar(4000) '$.Attributes.Bathroom',
    Basement varchar(4000) '$.Attributes.Basement',
    Attic varchar(4000) '$.Attributes.Attic'
) as Dataset
Go

注意事项： #

• 如果你的ndJSON文件使用的是**仅换行符(\n)**而不是回车+换行(\r\n)，只需要保留REPLACE(@ndJSON, CHAR(10), ',')这部分即可。
• 这种方法适合文件体积较小的场景，因为需要将整个文件内容加载到内存中处理。

方法二：逐行读取并解析每个JSON对象 #

对于大体积的ndJSON文件，逐行读取解析的性能更好。我们需要借助格式文件来指定行终止符，实现逐行读取：

第一步：创建格式文件（ndjson_format.xml） #

首先创建一个XML格式文件，用来告诉SQL Server如何分割每行数据：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <!-- 指定行终止符为回车+换行，如果文件只用\n，就改成TERMINATOR="\n" -->
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="4000"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="BulkColumn" xsi:type="SQLVARCHAR"/>
 </ROW>
</BCPFORMAT>

第二步：使用格式文件逐行解析ndJSON #

SELECT 
    JSON_VALUE(BulkColumn, '$.House') as House,
    JSON_VALUE(BulkColumn, '$.Attributes.Car') as Car,
    JSON_VALUE(BulkColumn, '$.Attributes.Door') as Door,
    JSON_VALUE(BulkColumn, '$.Attributes.Bathroom') as Bathroom,
    JSON_VALUE(BulkColumn, '$.Attributes.Basement') as Basement,
    JSON_VALUE(BulkColumn, '$.Attributes.Attic') as Attic
FROM OPENROWSET (
    BULK 'C:\examplepath\filename.ndJSON',
    FORMATFILE = 'C:\examplepath\ndjson_format.xml'
) as j

这种方法会逐行读取每个JSON对象，然后用JSON_VALUE直接提取需要的字段，避免了加载整个大文件到内存，适合处理大型ndJSON数据集。

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