How can I split JSON array into its own row?-CodePudding

I am trying to split NPI into their own rows. I can get the NPI values as a comma-separated string but I need to break them down into their own rows. I am able to get all the other fields because they have an object name with single values, NPI has multiple values listed out under a single object name.

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

The output is as follows

CodePudding user response：

You're almost there. In this expression

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

the symbol npi binds to provider_groups.npi which is a JSON array. You want the exploded values from the table aliased as npi which would be npi.value. So try

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