Parse JSON Data to SQL Table

Question

I have trawled the internet for the past two days without success so reluctantly here is my first question to the good people of Stack Overflow.

I am trying to transform JSON data to a SQL Server (2016) table but the data contains an array with no key. The JSON looks like so:

[
  {
    "year": 2016,
    "month": 1,
    "day": 1,
    "breakdownTotal": [
      "283082",
      "601184",
      "140120"
    ]
  },
  {
    "year": 2016,
    "month": 1,
    "day": 2,
    "breakdownTotal": [
      "354725",
      "760532",
      "177279"
    ]
  }
]

I can get the following table:

year    month   day
2016    1   1
2016    1   2

But ideally I would like to have:

year    month   day breakdown1  breakdown2  breakdown3
2016    1   1   283082  601184  140120
2016    1   2   354725  760532  177279

Though would be able to do something with:

year    month   day breakdown
2016    1   1   283082
2016    1   1   601184
2016    1   1   140120
2016    1   2   354725
2016    1   2   760532
2016    1   2   177279

This is similar to the example data at the top of this link: [https://msdn.microsoft.com/en-gb/library/dn921897.aspx][1] [1]: https://msdn.microsoft.com/en-gb/library/dn921897.aspx though unhelpfully, these is no mention of how to extract the info.

Does anybody have any tips on how I could achieve the desired result? Any help would be much appreciated.

Rob

Maybe this could help you? msdn.microsoft.com/en-us/library/dn921879.aspx — Danieboy
– Danieboy, Commented Mar 1, 2017 at 13:05
your "ideally I would like to have" is not a normalised database. Not a good structure. Your "would be able to do something with" example is much better. If you want later to output it like the first example, then you can do that separately using queries etc. — ADyson
– ADyson, Commented Mar 1, 2017 at 13:15

JohnLBevan · Accepted Answer · 2017-03-01 13:15:41Z

1

Try this (thanks to @Danieboy for the link in the comments):

DECLARE @json NVARCHAR(MAX)

SET @json='-paste your json here-';

SELECT *
FROM OPENJSON(@json)
WITH (   
    [year]   int '$.year'   
    ,[month] int '$.month'  
    ,[day] int '$.day'  
    ,[breakdown1] bigint '$.breakdownTotal[0]'  
    ,[breakdown2] bigint '$.breakdownTotal[1]'  
    ,[breakdown3] bigint '$.breakdownTotal[2]'  
 ) 
;

Sadly I don't have SQL 2016 so haven't been able to test.

answered Mar 1, 2017 at 13:15

JohnLBevan

24.8k16 gold badges107 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Foxinho Over a year ago

that works absolutely perfectly! Thanks for the speedy response, I'll mark this as answered.

JohnLBevan Over a year ago

No worries. ps. Per @ADyson's comments on your question, please note that the above code assumes there will always be 3 values in the breakdownTotal array; any less and you'll get nulls for the superfluous columns, any more and they won't be included.

Collectives™ on Stack Overflow

Parse JSON Data to SQL Table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related