question

SQLBen2020 avatar image
SQLBen2020 asked

Converting JSON data with multiple objects and arrays into SQL Server 2016 table

Hi,

I've hit a massive dead end trying to read the below JSON into SQL Server 2016. I need all the data in a flattened table. I think the issue maybe the config key within the Values object. Any advice much appreciated.

<br>
sql queryjson
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What did you try so far?

0 Likes 0 ·

1 Answer

· Write an Answer
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

As you don't specify how you want to display the data, it's pretty hard to tell how to write the t-sql to get the data into that format.
I can only point you into the direction of good resources on json data in SQL Server. I think Damir Matesic's blog post on reading JSON data is pretty good. Look at the last example of reading a JSON structure with multiple levels for example.

https://blog.matesic.info/post/Read-JSON-data-in-MS-SQL

And given Damir's example JSON, here's an example on how to get sub-values from a structure using JSON_VALUE and OPENJSON.

DECLARE @JSON_data NVARCHAR(MAX) = N'{
                
"Name": "Damir Matešić",
"BlogURL": "http:\/\/blog.matesic.info",
"Born": 1979,
"Spouse":null,
"BornAfterWoodstock": true,
"FavoriteDrinks": [
{"Name": "Gin and tonic","Drink": "Occasionally"},
{"Name": "Craft beer","Drink": "Occasionally"},
{"Name": "Coffe with milk","Drink": "Daily"},
{"Name": "Cold water","Drink": "Daily"}],
"Parents": {"Mom": "Iva","Dad": "Boris"}
}';
SELECT
Name,
[Blog URL],
Born,
Spouse,
Data.[Favorite drinks],
Mom = JSON_VALUE(Data.Parents,'$.Mom'),
Dad = JSON_VALUE(Data.Parents,'$.Dad')
FROM OPENJSON(@JSON_data) WITH (
Name NVARCHAR(256) '$.Name',
[Blog URL] NVARCHAR(256) '$.BlogURL',
Born INT '$.Born',
Spouse NVARCHAR(256) '$.Spouse',
[Favorite drinks] NVARCHAR(MAX) '$.FavoriteDrinks' AS JSON,
Parents NVARCHAR(MAX) '$.Parents' AS JSON
) Data
;

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.