Post reply

Import JSON in SSIS with Column Row Transformed

  • 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"}}

  • January 15, 2021 at 2:10 am

    #3833624

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

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

  • January 15, 2021 at 6:40 pm

    #3833900

    Try this, but I'm not going to guarantee any performance, but at least it flattens it out

    declare @x varchar(max) = '{"STATE":{"0":"CO","1":"CO","2":"CO","3":"CO","10000
    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 4 days, 8 hours ago by  Mike01.
    • This reply was modified 4 days, 6 hours 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/

  • January 16, 2021 at 12:40 am

    #3833997

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

Viewing 4 posts - 1 through 4 (of 4 total)

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