SQL Server从JSON文件插入数据时记录数超出预期问题咨询

问题根因 #

查询产生重复数据的核心原因是第一个OPENJSON (@JSON)没有指定WITH结构，会直接返回JSON根节点下所有顶层键的行记录：你的示例JSON根节点共有6个顶层键，因此这一步会返回6行数据。后续两次CROSS APPLY分别会为每一行匹配1次根属性、3次category_track数组值，最终总记录数为613=18行，和你遇到的现象一致。
除此之外你还存在两处语法问题：

• 读取category_track的路径多了多余的引号，正确路径应为$.category_track而非$."category_track"
• 不需要重复调用OPENJSON(@JSON)读取根属性，冗余的调用会放大重复行数

修正后的查询语句 #

Declare @JSON VARCHAR(max)
SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'C:\User\Desktop\whatsapp.json', SINGLE_CLOB) import

INSERT INTO DATAS(pol_id, service_name, source, parsed_version_url, version_date, data)
select 4, service_name, source, parsed_version_url, version_date, data
-- 直接读取根节点属性，仅返回1行根属性记录
from openjson (@JSON) with (
        service_name VARCHAR (50) '$.name_service',
        source VARCHAR (50) '$.source',
        parsed_version_url VARCHAR (100) '$.source',
        -- 如果日期转换报错，可替换为下方注释行指定dd/mm/yyyy格式解析
        -- version_date DATE '$.version_date' 改为 CONVERT(DATE, JSON_VALUE(@JSON, '$.version_date'), 103) as version_date
        version_date DATE '$.version_date'
        )
-- 关联category_track数组，每个数组值对应一行，1*3=3行符合预期
cross apply openjson (@JSON, '$.category_track') with (
        data varchar (50) '$'
        );

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