Home:ALL Converter>How do I load elements from a nested, comma separated JSON array into a SQL table

How do I load elements from a nested, comma separated JSON array into a SQL table

Ask Time:2021-09-22T04:47:40         Author:Don

Json Formatter

I have a JSON array that I need to load into a SQL Server 2019 table. I'm having trouble with the language field which contains multiple values in square brackets, separated by commas. If I add the [0] key I can grab the first value, but I need all of the values in the field and the number of entries can vary. Removing the [0] ket just returns NULLs.

The JSON data is formatted like this:

"npi": "10009",
"type": "INDIVIDUAL",
"name": {
  "prefix": "",
  "first": "John",
  "middle": "S",
  "last": "Smith",
  "suffix": ""
"addresses": [
    "address": "91820 SE Oak Rd",
    "address_2": "",
    "city": "Dundee",
    "state": "OR",
    "zip": "97555",
    "phone": "8558675309"
"specialty": [ "Family Medicine" ],
"accepting": "not accepting",
"gender": "Female",
"plans": [
    "plan_id_type": "PLAN-ID",
    "plan_id": "717420005",
    "network_tier": "TIER-ONE",
    "years": [ 2021 ]

    "plan_id_type": "PLAN-ID",
    "plan_id": "71255203",
    "network_tier": "TIER-ONE",
    "years": [ 2021 ]
"languages": [ "English", "EN (English)", "SP (Spanish)" ],
"last_updated_on": "2021-08-04" }

The T-sql that comes closest to what I need is

    Declare @ProviderDirCO varchar (max)

FROM OPENROWSET (BULK 'F:\Transfer\Provider_Who.JSON', SINGLE_CLOB) json 
 --INSERT INTO Providers.ProviderDirCO
    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,
    JSON_VALUE(a.value, '$.languages[0]') 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

How do I get all of the language type listed in the array to import into SQL?

Author:Don,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/69275382/how-do-i-load-elements-from-a-nested-comma-separated-json-array-into-a-sql-tabl