如何将嵌套逗号分隔JSON数组元素加载至SQL Server 2019表中

你需要新增一个CROSS APPLY调用OPENJSON拆解languages数组，和你当前处理addresses、plans数组的逻辑完全一致，修改后的T-SQL如下：

Declare @ProviderDirCO varchar (max)
SELECT @ProviderDirCO=BULKCOLUMN
FROM OPENROWSET (BULK 'F:\Transfer\Provider_Who.JSON', SINGLE_CLOB) json 
 --INSERT INTO Providers.ProviderDirCO
 SELECT 
    JSON_VALUE(a.value, '$.npi') as NPI,
    JSON_VALUE(a.value, '$.type') as type,
    JSON_VALUE(a.value, '$.name.prefix') as prefix,
    JSON_VALUE(a.value, '$.name.first') as first,
    JSON_VALUE(a.value, '$.name.middle') as middle,
    JSON_VALUE(a.value, '$.name.last') as last,
    JSON_VALUE(a.value, '$.name.suffix') as suffix,
    JSON_VALUE(b.value, '$.address') as address,
    JSON_VALUE(b.value, '$.address_2') as Address_2,
    JSON_VALUE(b.value, '$.city') as City,
    JSON_VALUE(b.value, '$.state') as State,
    JSON_VALUE(b.value, '$.zip') as Zip,
    JSON_VALUE(b.value, '$.phone') as Phone,
    JSON_VALUE(a.value, '$.specialty[0]' ) as Specialty,
    JSON_VALUE(a.value, '$.accepting') as Accepting,
    JSON_VALUE(a.value, '$.gender') as gender,
    d.value as languages, -- 直接取拆解后的语言值
    JSON_VALUE(a.value, '$.last_updated_on') as last_updated_on,
    JSON_VALUE(c.value, '$.plan_id_type') as plan_id_type,
    JSON_VALUE(c.value, '$.plan_id') as plan_id,
    JSON_VALUE(c.value, '$.network_tier') as network_tier,
    JSON_VALUE(c.value, '$.years[0]') as years
FROM OPENJSON(@ProviderDirCO ) as a
CROSS APPLY OPENJSON(a.value, '$.addresses') as b
CROSS APPLY OPENJSON(a.value, '$.plans') as c
-- 新增拆解languages数组的逻辑
CROSS APPLY OPENJSON(a.value, '$.languages') as d

可选适配场景 #

• 若需要保留无语言值的记录，将CROSS APPLY OPENJSON(a.value, '$.languages')替换为OUTER APPLY OPENJSON(a.value, '$.languages')即可，缺失语言的记录对应字段会返回NULL。
• 若不需要拆分多行，希望把同一主体的所有语言合并为逗号分隔的字符串存储在单个字段中，可使用聚合函数实现，示例如下：

-- 其他SELECT字段保持不变，替换languages字段写法，最后加GROUP BY
SELECT 
    -- 其余字段和原写法完全一致
    STRING_AGG(d.value, ', ') as languages,
    -- 其余字段和原写法完全一致
FROM OPENJSON(@ProviderDirCO ) as a
CROSS APPLY OPENJSON(a.value, '$.addresses') as b
CROSS APPLY OPENJSON(a.value, '$.plans') as c
OUTER APPLY OPENJSON(a.value, '$.languages') as d
-- 把SELECT里除聚合字段外的所有字段都加到GROUP BY中
GROUP BY 
    JSON_VALUE(a.value, '$.npi'),
    JSON_VALUE(a.value, '$.type'),
    JSON_VALUE(a.value, '$.name.prefix'),
    JSON_VALUE(a.value, '$.name.first'),
    JSON_VALUE(a.value, '$.name.middle'),
    JSON_VALUE(a.value, '$.name.last'),
    JSON_VALUE(a.value, '$.name.suffix'),
    JSON_VALUE(b.value, '$.address'),
    JSON_VALUE(b.value, '$.address_2'),
    JSON_VALUE(b.value, '$.city'),
    JSON_VALUE(b.value, '$.state'),
    JSON_VALUE(b.value, '$.zip'),
    JSON_VALUE(b.value, '$.phone'),
    JSON_VALUE(a.value, '$.specialty[0]' ),
    JSON_VALUE(a.value, '$.accepting'),
    JSON_VALUE(a.value, '$.gender'),
    JSON_VALUE(a.value, '$.last_updated_on'),
    JSON_VALUE(c.value, '$.plan_id_type'),
    JSON_VALUE(c.value, '$.plan_id'),
    JSON_VALUE(c.value, '$.network_tier'),
    JSON_VALUE(c.value, '$.years[0]')

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