如何将JSON中的NPI数组拆分为单独数据行？

你的SQL代码已经正确实现了将NPI数组拆分为单独行的需求，以下是对代码逻辑的说明及优化建议：

核心实现逻辑 #

通过多层OPENJSON结合CROSS APPLY逐层解析JSON嵌套结构：

1. 先解析顶层的provider_references数组
2. 再解析每个provider_references下的provider_groups数组
3. 解析provider_groups中的tin对象和npi数组，最后通过CROSS APPLY OPENJSON(provider_groups.npi)将NPI数组的每个元素拆分为单独行，同时保留与其他字段的关联关系

完整优化后SQL代码 #

declare @json NVARCHAR(MAX)
SET @json = N'{
  "reporting_entity_name": "ABC",
  "reporting_entity_type": "Third Party Administrator",
  "last_updated_on": "2022-10-05",
  "version": "1.0.0",
  "provider_references": [
    {
      "provider_group_id": 19463,
      "provider_groups": [
        {
          "npi": [1811971955,1013223874,1588677066],
          "tin": {"type": "ein","value": "000000000"}
        },
        {
          "npi": [1245794387,1437585882,1932631751,1932482296,1508376864,1033654181,1093166530,1609300672],
          "tin": {"type": "ein","value": "461621659"}
        },
        {
          "npi": [1245573369,1528219359,1083076897],
          "tin": {"type": "ein","value": "132655001"}
        },
        {
          "npi": [1134452170],
          "tin": {"type": "ein","value": "472304826"}
        },
        {
          "npi": [1194250274],
          "tin": {"type": "ein","value": "113511743"}
        },
        {
          "npi": [1427558378],
          "tin": {"type": "ein","value": "824264835"}
        },
        {
          "npi": [1972681484,1508846932],
          "tin": {"type": "ein","value": "134009634"}
        },
        {
          "npi": [1578235743,1770726788],
          "tin": {"type": "ein","value": "872533474"}
        },
        {
          "npi": [1619166899,1871648949],
          "tin": {"type": "ein","value": "113531019"}
        }
      ]
    }
  ]
}';

drop table if exists newtable;
select 
  provider_group_id,
  tin.[type],
  tin.[value],
  npi.value as npi  -- 明确指定取数组元素的值，代码可读性更强
into newtable
from openjson (@json)
with (provider_references nvarchar(max) as json) as topinfo
cross apply openjson (topinfo.provider_references)
with (
  provider_group_id int,  -- 改为int类型匹配JSON中的数值类型，避免隐式转换
  provider_groups nvarchar(max) as json
) as provider_references
cross apply openjson (provider_references.provider_groups)
with (
  npi nvarchar(max) as json,
  tin nvarchar(max) as json
) as provider_groups
cross apply openjson (provider_groups.tin)
with (
  [type] varchar(3),
  [value] varchar(10)
) as tin
cross apply openjson (provider_groups.npi) as npi;

select * from newtable;

输出结果说明 #

执行后生成的newtable中，每个NPI数组元素都会成为单独一行，且与所属的provider_group_id、税号类型(type)和税号值(value)正确关联，示例结果如下：

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