Accessing Nested JSON Array Data using OpenJSON

[pabowen]

Hello everyone, and thank you in advance for your help.

I have JSON files that I am working on importing into SQL. I am able to access the general data, but there is some data where it contains an Array of data (just a listing) which may be 0 rows, or 100. I am unable to determine how to reference this data in order to call it correctly.

Here is my sample data to work with.

{
  "Tickets": [
    {
      "id": 866883,
      "type": "problem",
      "priority": "urgent",
      "status": "closed",
      "follower_ids": [
        7967649388,
        7967864608,
        365003995267,
        7897694567,
        7897776387,
        7899032227,
        7969040908,
        7898078107
      ]
    }
  ]
}

As you can see the field 'follower_ids' is an array. attempting to call this as in any other field simply returns a null.

Here is the code I am using.

Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'c:\temp\array_test.json', SINGLE_CLOB) as j

Select * 
from openjson (@JSON, '$.Tickets'  )
WITH ( 
id varchar(10) , 
type varchar(25),
priority varchar(25),
status varchar(25),
follower_ids nvarchar(255)  -- << This line is where I am unable to determine the proper call
)

Does anyone know how to call a nested array using OpenJSON?

Thanks,
Patrick

 

[jbenson001]

There is an example here that may help:

https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-2017

Instead of trying to do everything in on SQL statement, why not break it up?
First get the data with the query you have ( select into a temp table ). Instead of worrying about breaking up the array, just pull it into a VARCHAR(MAX) column with comma separated values then
select that data into another table using a function to parse out the comma separated values from the column. There have been many examples here on how to do that as well as online.

I hope this helps