SQL解析含嵌套数组的JSON列：查询无法得到预期结果求助

你的SQL查询无法得到预期结果的核心原因是：JSON中的categories是二维嵌套数组（外层数组包裹着一个内层数组），原SQL仅对categories的外层数组做了解析，没有处理内层的数组结构，导致无法提取单个数值。

修正后的SQL代码 #

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\temp\test.json', SINGLE_CLOB) import

INSERT INTO dbo.TABLE
SELECT
    metadata.[id],
    categories.[categories1],
    categories.[categories2],
    categories.[categories3],
    getdate() as [dt_imp] 
FROM OPENJSON (@JSON)
WITH(
    [items] NVARCHAR(MAX) AS JSON
) AS Data

OUTER APPLY OPENJSON([Data].[items])
WITH(
    [id] INT, -- 用INT类型匹配JSON中的数字id，比NVARCHAR更合理
    [categories] NVARCHAR(MAX) AS JSON
) AS metadata

-- 直接通过JSON路径定位到categories的内层数组（外层数组的第一个元素）
OUTER APPLY OPENJSON(metadata.categories, '$[0]')
WITH(
    categories1 INT '$[0]', -- 取内层数组第1个元素（索引从0开始）
    categories2 INT '$[1]', -- 取内层数组第2个元素
    categories3 INT '$[2]'  -- 取内层数组第3个元素
) AS categories

关键修正点解释 #

1. 处理嵌套数组：通过OPENJSON(metadata.categories, '$[0]')直接定位到categories外层数组中的第一个（也是唯一一个）内层数组，避免多一层不必要的数组解析。
2. 提取数组元素：使用JSON路径$[0]、$[1]、$[2]分别获取内层数组的三个元素，对应到categories1、categories2、categories3字段。
3. 类型优化：将id和分类字段的类型从NVARCHAR(MAX)改为INT，匹配JSON中的数字类型，提升查询效率和数据准确性。

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