如何將JSON陣列拆分為自己的行？

我正在嘗試將 NPI 拆分為自己的行。我可以將 NPI 值作為逗號分隔的字串獲取，但我需要將它們分解為自己的行。我能夠獲取所有其他欄位，因為它們具有帶有單個值的物件名稱，NPI 在單個物件名稱下列出了多個值。

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
into newtable
from   openjson (@json)
with
(
   -- reporting_entity_name nvarchar(5),
   provider_references nvarchar(max) as json
) as topinfo
cross apply openjson (topinfo.provider_references)
with
( 
provider_group_id varchar(10),
  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

輸出如下

uj5u.com熱心網友回復：

您快到了。在這個運算式中

select provider_group_id,tin.type,tin.value,npi

npi所系結的符號provider_groups.npi是 JSON 陣列。您希望表中的分解值別名npi為npi.value. 所以試試

select provider_group_id,tin.type,tin.value tin, npi.value npi
into newtable
. . .

標籤：jsonsql服务器sql-server-2019

上一篇：從json陣列中檢索與變數值匹配的所有值

下一篇：mysql中的json_search和json_remove