Import JSON in SSIS with Column Row Transformed

Question

Post reply

Import JSON in SSIS with Column Row Transformed

ClockSpeed8

Old Hand

Points: 344
More actions
January 14, 2021 at 1:45 am

#3832231
I’m attempting to modify the code on this SQL Server Central article "Approaches to Import JSON in SSIS (SQL Server 2016+) Part 2" using the methods described under “Using SQL Functionality”. I'm successfully using this with other JSON files formatted in a more standard way. However, the JSON flat file that I’m tasked with bringing into SQL Server now is in a transformed format of sorts... which list the 1st column and all it’s data, then moves to the next. I’m having trouble determining an easy method for bringing this in. I'm thinking columns need to be transformed to rows basically, but (if possible) I don't want to have to name each column/row 1 through 1000001. Any suggestions, and many thanks in advance for your help!
The JSON looks similar to this, but with more than 1M "rows" and 40 columns, here's an example of 3 of the columns:
```
{"STATE":{"0":"CO","1":"CO","2":"CO","3":"CO", ................ "1000001":"CO"},
"CITY":{"0":"Denver","1":"Denver","2":"Denver","3":"Denver", ................ "1000001":"Vail"},
"ZIP":{"0":"80123","1":"80123","2":"80123","3":"80123", ................ "1000001":"81631"}}
```

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Mike01 SSChampion Points: 11395 More actions · Answer 2

Try this, but I'm not going to guarantee any performance, but at least it flattens it out. The Code that was here was not copied correctly. Here is the updated code. Still using OpenJson

declare @x varchar(max) = '{"STATE":{"0":"CO","1":"CO","2":"CO","3":"CO","1000001":"CO"},
"CITY":{"0":"Denver","1":"Denver","2":"Denver","3":"Denver","1000001":"Vail"},
"ZIP":{"0":"80123","1":"80123","2":"80123","3":"80123","1000001":"81631"}}'
;with cte as
(select r.[Key], r.[Value], r.ItemNumber as GroupNum, r.Item as GroupItem, z.ItemNumber, z.Item
from (
select * from (
select , Replace(Replace(Replace([Value],'"',''),'{',''), '}','') as [Value]
from OPENJSON(@x, '$')) x
cross apply [dbo].[DelimitedSplit8K] ([value], ',') Z
) r
cross apply [dbo].[DelimitedSplit8K] (r.Item, ':') Z)


select s.Item as State, c.Item as City, z.Item as Zip
from cte s
join cte c
on c.GroupNum = s.GroupNum
and c.ItemNumber = s.ItemNumber
and c. = 'CITY'
join cte z
on z.GroupNum = c.GroupNum
and Z.ItemNumber = c.ItemNumber
and z. = 'ZIP'
where s.[Key] = 'STATE'
and s.ItemNumber = 2

This reply was modified 3 years, 10 months ago by Mike01.
This reply was modified 3 years, 10 months ago by Mike01.
This reply was modified 3 years, 10 months ago by Mike01.

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ClockSpeed8 Old Hand Points: 344 More actions · Answer 3

Thanks Mike for your response, I'll dig into this next week and see what I can make happen.

ClockSpeed8 Old Hand Points: 344 More actions · Answer 4

Thanks again Mike, apparently my enterprise has not updated to a version that supports OPENJSON. I am able to use JSON_VALUE however. Any bright ideas? I really appreciate your time!

Mike01 SSChampion Points: 11395 More actions · Answer 5

What version of SQL are you using? I thought they were both available from 2016 forward. I copied and pasted my code above and stepped on a couple of fields. I updated the code above so it is working now. See if that helps.

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ClockSpeed8 Old Hand Points: 344 More actions · Answer 6

Thanks Mike, it is 2016, however I've learned since my initial post that the database was migrated from 2008 and therefore has kept it's Compatibility Level 100 which does not support OPENJSON. Compatibility Level 130 (the default for SQL 2016) only applies to databases created in 2016... or if you go through the effort of testing compatibility and performance to upgrade migrated databases to 130, which my enterprise is unwilling to put effort in to. So for now I don't have the OPENJSON function :/

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

You can get around that issue by creating a new database at 130 compatibility - and place your code in that database. You will need to use 3-part naming or synonyms in your code to access the other database(s) - but you will have access to the new 2016 features from that database.

This can be a very simple (and small) database to host just the code, views and functions needed to support your code.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs